Performance bei Query mit GROUP BY, COUNT und JOIN

Hallo,

ich habe in MySQL 5.0 (MyISAM) folgende Query in einer Datenbank mit
100.000 Einträgen in der Master Table companysites und 170.000
Einträgen in der Detail Table contacts.

SELECT companysites.company_name,COUNT(contacts.company_id)
FROM contacts INNER JOIN companysites USING (company_id)
GROUP BY contacts.company_id

company_id ist der Primärschlüssel in companysites und der
Fremdschlüssel in contacts.
In contacts ist company_id indiziert.

Hier der Explain:
id select_type tyble type possible_keys key key_len ref rows Extra
1 SIMPLE contacts index company_id company_id 4 NULL 174858 Using
index; Using temporary; Using filesort
1 SIMPLE companysites eq_ref PRIMARY PRIMARY 4 contacts.company_id 1

Die Ausführung der Query dauert über 10 Sekunden, wenn ich mit LIMIT
10 und OFFSET arbeite, dauert es immer noch mehrere Sekunden.

Wie kann das schneller gehen?

Eine Query, welche ausschließlich die table contacts verwendet ist
tatsächlich deutlich schneller (deutlich unter 1s, was schon
akteptabel wäre).

Bsp:
SELECT company_id,COUNT(company_id)
FROM contacts
GROUP BY company_id

Ich benötige aber u.a. den company_name aus companysites.
In der erweiterten Form der Query sollen Daten auch noch über weitere
JOINS auf companysites gefiltert werden, d.h. die Table companysites
kann ich in der Query nicht einfach außen vor lassen ;)

Ich hatte bereits mit Subselects und Views gearbeitet, was die
Performance aber nicht wesentlich verbessert hat, was kann ich sonst
noch tun?

Danke für Eure Hilfe!

Gruß

Thilo Hermann

Re: Performance bei Query mit GROUP BY, COUNT und JOIN

Thilo Hermann schrieb:

> Hier der Explain:
> id select_type tyble type possible_keys key key_len ref rows Extra
> 1 SIMPLE contacts index company_id company_id 4 NULL 174858 Using
> index; Using temporary; Using filesort
> 1 SIMPLE companysites eq_ref PRIMARY PRIMARY 4 contacts.company_id 1

Tipp #1: Niemals Meldungen, egal welcher Art, abtippen, sondern immer
mit Copy&Paste übernehmen. Das vermeidet Tippfehler.

Tipp #2: Solche Anfragen besser mit '\G' statt ';' abschließen. Dann
kommen die Ergebnisse in Usenet-verträglicher Form und werden nicht so
verstümmelt wie die obige Tabelle.

Gruß. Claus

Re: Performance bei Query mit GROUP BY, COUNT und JOIN

On 28 Nov., 08:57, Claus Reibenstein <4spammerso...@web.de> wrote:
> Thilo Hermann schrieb:
>
>
> > id select_type tyble type possible_keys key key_len ref =
rows Extra

Tyble - auweia! :D
Hast recht, Copy/Paste wäre besser gewesen, mein Editor (SQLyog) hatte
da bei den Spaltennamen nicht mitgemacht.

>
> Tipp #2: Solche Anfragen besser mit '\G' statt ';' abschließen.

Sorry, das war mein erster Post im Usenet überhaupt. Ich versuchs
nochmal:
+----+-------------+--------------+--------+---------------
+------------+-------
--+---------------------------------------+--------
+----------------------------
------------------+
| id | select_type | table | type | possible_keys |
key | key_le
n | ref | rows | Extra
|
+----+-------------+--------------+--------+---------------
+------------+-------
--+---------------------------------------+--------
+----------------------------
------------------+
| 1 | SIMPLE | contacts | index | company_id |
company_id | 4
| NULL | 174858 | Using index;
Using temporar
y; Using filesort |
| 1 | SIMPLE | companysites | eq_ref | PRIMARY |
PRIMARY | 4
| emc_events_online.contacts.company_id | 1 |
|
+----+-------------+--------------+--------+---------------
+------------+-------

Re: Performance bei Query mit GROUP BY, COUNT und JOIN

Großes Sorry an alle Mitleser! Hier der dritte Versuch des Explain von

SELECT companysites.company_name,COUNT(contacts.company_id)
FROM contacts INNER JOIN companysites USING (company_id)
GROUP BY contacts.company_id

| id | select_type | table | type | possible_keys |
key | key_len | ref | rows
| Extra |
| 1 | SIMPLE | contacts | index | company_id |
company_id | 4 | NULL | 174858 |
Using index; Using temporary; Using filesort |
| 1 | SIMPLE | companysites | eq_ref | PRIMARY |
PRIMARY | 4 | emc_events_online.contacts.company_id | 1 |

Gruß
Thilo Hermann

Re: Performance bei Query mit GROUP BY, COUNT und JOIN

On Tue, 27 Nov 2007 23:00:37 -0800, Thilo Hermann wrote:

> Hallo,
>
> ich habe in MySQL 5.0 (MyISAM) folgende Query in einer Datenbank mit
> 100.000 Einträgen in der Master Table companysites und 170.000 Einträgen
> in der Detail Table contacts.
>
> SELECT companysites.company_name,COUNT(contacts.company_id) FROM
> contacts INNER JOIN companysites USING (company_id) GROUP BY
> contacts.company_id

Mach da mal das hier draus:
SELECT
cs.company_name, COUNT(*)
FROM
companysites cs
INNER JOIN
contacts c USING (company_id)
GROUP BY
cs.companyid

GROUP BY wenn möglich immer auf die Spalte die ne
Eindeutigkeitseinschränkung hat. Und COUNT(contacts.company_id) war
witzlos, weil contacts.company_id eh nicht NULL werden kann.

> company_id ist der Primärschlüssel in companysites und der
> Fremdschlüssel in contacts.
> In contacts ist company_id indiziert.
>
> Hier der Explain:
> id select_type tyble type possible_keys key key_len
ref rows Extra 1
> SIMPLE contacts index company_id company_id
4 NULL 174858 Using index;
> Using temporary; Using filesort 1 SIMPLE companysites eq_ref
PRIMARY
> PRIMARY 4 contacts.company_id 1
>
> Die Ausführung der Query dauert über 10 Sekunden, wenn ich mit LIMIT 10
> und OFFSET arbeite, dauert es immer noch mehrere Sekunden.
>
> Wie kann das schneller gehen?
>
> Eine Query, welche ausschließlich die table contacts verwendet ist
> tatsächlich deutlich schneller (deutlich unter 1s, was schon akteptabel
> wäre).
>
> Bsp:
> SELECT company_id,COUNT(company_id)
> FROM contacts
> GROUP BY company_id
>
> Ich benötige aber u.a. den company_name aus companysites. In der
> erweiterten Form der Query sollen Daten auch noch über weitere JOINS auf
> companysites gefiltert werden, d.h. die Table companysites kann ich in
> der Query nicht einfach außen vor lassen ;)
>
> Ich hatte bereits mit Subselects und Views gearbeitet, was die
> Performance aber nicht wesentlich verbessert hat, was kann ich sonst
> noch tun?

SELECT
cs.company_name, c.c
FROM
(SELECT
company_id, COUNT(*) c
FROM
contacts
GROUP BY
company_id) c
INNER JOIN
companysites cs USING (company_id)

Sollte ähnlich schnell sein, allerdings nur mit halbwegs aktuellen MySQL
Versionen, ältere hatten nen Bug bei denen GROUP BY Optimierungen in
Subqueries grundsätzlich übern Jordan gingen.

Björn

Re: Performance bei Query mit GROUP BY, COUNT und JOIN

Thilo Hermann schrieb:
> Großes Sorry an alle Mitleser! Hier der dritte Versuch des Explain von
>
> SELECT companysites.company_name,COUNT(contacts.company_id)
> FROM contacts INNER JOIN companysites USING (company_id)
> GROUP BY contacts.company_id
>
> | id | select_type | table | type | possible_keys |
> key | key_len | ref | rows
> | Extra |
> | 1 | SIMPLE | contacts | index | company_id |
> company_id | 4 | NULL | 174858 |
> Using index; Using temporary; Using filesort |
> | 1 | SIMPLE | companysites | eq_ref | PRIMARY |
> PRIMARY | 4 | emc_events_online.contacts.company_id | 1 |
>

Nochmal zum Mitlesen:

Du sollst die Query in mysql mit \G abschließen, nicht mit ";". Also

EXPLAIN SELECT companysites.company_name,COUNT(contacts.company_id)
FROM contacts INNER JOIN companysites USING (company_id)
GROUP BY contacts.company_id\G

mit Copy und Paste in mysql reinwerfen. Das sollte doch machbar sein?

Re: Performance bei Query mit GROUP BY, COUNT und JOIN

Danke Björn, das hat weitergeholfen.

On 28 Nov., 10:08, Björn Steinbrink wrote:
> On Tue, 27 Nov 2007 23:00:37 -0800, Thilo Hermann wrote:
> > SELECT companysites.company_name,COUNT(contacts.company_id) FROM
> > contacts INNER JOIN companysites USING (company_id) GROUP BY
> > contacts.company_id
>
> Mach da mal das hier draus:
> SELECT
> cs.company_name, COUNT(*)
> FROM
> companysites cs
> INNER JOIN
> contacts c USING (company_id)
> GROUP BY
> cs.companyid
>

Das hatte keinen Performancegewinn gebracht.
Interessanterweise funktionierte diese Query kombiniert mit LIMIT und
OFFSET aber deutlich schneller, als ich LEFT JOIN statt INNER JOIN
verwendet habe, was in diesem Fall das Ergebnis nicht beeinflusst hat:

SELECT
cs.company_name, COUNT(*)
FROM
companysites cs
LEFT JOIN
contacts c USING (company_id)
GROUP BY
cs.company_id
LIMIT 10 OFFSET 123

Ausführungszeit: 90ms, zum Vergleich: die selbe Query mit INNER JOIN
und LIMIT dauerte regelmäßig länger als 5s
Kann das mit Caching der Query zusammenhängen?

[...]
> > Ich hatte bereits mit Subselects und Views gearbeitet, was die
> > Performance aber nicht wesentlich verbessert hat, was kann ich sonst
> > noch tun?
>
> SELECT
> cs.company_name, c.c
> FROM
> (SELECT
> company_id, COUNT(*) c
> FROM
> contacts
> GROUP BY
> company_id) c
> INNER JOIN
> companysites cs USING (company_id)
>
> Sollte ähnlich schnell sein, allerdings nur mit halbwegs aktuellen MySQL=

> Versionen, ältere hatten nen Bug bei denen GROUP BY Optimierungen in
> Subqueries grundsätzlich übern Jordan gingen.

Diese Query war mit MySQL 5.0.45 deutlich schneller
(unter 0.3s-0.5s), selbst wenn alle Daten abgefragt wurden.

Die Option \G (statt \g) ist jetzt auch klar geworden, danke
Christian, hier der Vollständigkeit halber nochmal nochmal der richtig
formatierte Explain der ursprünglichen Query ;)

mysql> EXPLAIN SELECT
companysites.company_name,COUNT(contacts.company_id)
FROM contacts INNER JOIN companysites USING (company_ID) GROUP BY
companysites.com
pany_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: contacts
type: index
possible_keys: company_id
key: company_id
key_len: 4
ref: NULL
rows: 174858
Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: companysites
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: contacts.company_id
rows: 1
Extra:
2 rows in set (0.00 sec)

Gruß Thilo

Re: Performance bei Query mit GROUP BY, COUNT und JOIN

On Wed, 28 Nov 2007 02:12:41 -0800, Thilo Hermann wrote:

> Danke Björn, das hat weitergeholfen.
>
> On 28 Nov., 10:08, Björn Steinbrink wrote:
>> On Tue, 27 Nov 2007 23:00:37 -0800, Thilo Hermann wrote:
>> > SELECT companysites.company_name,COUNT(contacts.company_id) FROM
>> > contacts INNER JOIN companysites USING (company_id) GROUP BY
>> > contacts.company_id
>>
>> Mach da mal das hier draus:
>> SELECT
>> cs.company_name, COUNT(*)
>> FROM
>> companysites cs
>> INNER JOIN
>> contacts c USING (company_id)
>> GROUP BY
>> cs.companyid
>>
>>
> Das hatte keinen Performancegewinn gebracht. Interessanterweise
> funktionierte diese Query kombiniert mit LIMIT und OFFSET aber deutlich
> schneller, als ich LEFT JOIN statt INNER JOIN verwendet habe, was in
> diesem Fall das Ergebnis nicht beeinflusst hat:
>
> SELECT
> cs.company_name, COUNT(*)
> FROM
> companysites cs
> LEFT JOIN
> contacts c USING (company_id)
> GROUP BY
> cs.company_id
> LIMIT 10 OFFSET 123
>
> Ausführungszeit: 90ms, zum Vergleich: die selbe Query mit INNER JOIN und
> LIMIT dauerte regelmäßig länger als 5s Kann das mit Caching der Query
> zusammenhängen?

Hm, könntest du uns mal die EXPLAIN Ausgabe für beide Varianten (also mit
und ohne Limit) zeigen? Da die LIMIT Angabe jetzt ne Auswirkung hat, geh
ich davon aus, dass die Join-Reihenfolge jetzt besser ist, aber die GROUP
BY Optimierung noch hinkt... IIRC kann MySQL das noch besser, evtl. mal
COUNT(c.*) versuchen? *confused*


> [...]
>> > Ich hatte bereits mit Subselects und Views gearbeitet, was die
>> > Performance aber nicht wesentlich verbessert hat, was kann ich sonst
>> > noch tun?
>>
>> SELECT
>> cs.company_name, c.c
>> FROM
>> (SELECT
>> company_id, COUNT(*) c
>> FROM
>> contacts
>> GROUP BY
>> company_id) c
>> INNER JOIN
>> companysites cs USING (company_id)
>>
>> Sollte ähnlich schnell sein, allerdings nur mit halbwegs aktuellen
>> MySQL Versionen, ältere hatten nen Bug bei denen GROUP BY Optimierungen
>> in Subqueries grundsätzlich übern Jordan gingen.
>
> Diese Query war mit MySQL 5.0.45 deutlich schneller (unter 0.3s-0.5s),
> selbst wenn alle Daten abgefragt wurden.

Na immerhin, 50% richtig, das reicht für ne 4, bin versetzt ;-)

Björn

Re: Performance bei Query mit GROUP BY, COUNT und JOIN

On 28 Nov., 11:30, Björn Steinbrink wrote:
> On Wed, 28 Nov 2007 02:12:41 -0800, Thilo Hermann wrote:
> > Danke Björn, das hat weitergeholfen.
>
> > On 28 Nov., 10:08, Björn Steinbrink wrote:
> Hm, könntest du uns mal die EXPLAIN Ausgabe für beide Varianten (also =
mit
> und ohne Limit) zeigen?

Das wäre einen Versuch wert:

mit Limit:
mysql> use emc_events_online;
Database changed
mysql> EXPLAIN SELECT
-> cs.company_name, COUNT(*)
-> FROM
-> companysites cs
-> LEFT JOIN
-> contacts c USING (company_id)
-> GROUP BY
-> cs.company_id
-> LIMIT 10 OFFSET 123\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cs
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 117230
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: company_id
key: company_id
key_len: 4
ref: emc_events_online.cs.company_id
rows: 19
Extra: Using index
2 rows in set (0.40 sec)

Ohne Limit:
mysql> EXPLAIN SELECT
-> cs.company_name, COUNT(*)
-> FROM
-> companysites cs
-> LEFT JOIN
-> contacts c USING (company_id)
-> GROUP BY
-> cs.company_id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: cs
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 117230
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: ref
possible_keys: company_id
key: company_id
key_len: 4
ref: emc_events_online.cs.company_id
rows: 19
Extra: Using index
2 rows in set (0.00 sec)


> Da die LIMIT Angabe jetzt ne Auswirkung hat, geh
> ich davon aus, dass die Join-Reihenfolge jetzt besser ist, aber die GROUP
> BY Optimierung noch hinkt... IIRC kann MySQL das noch besser, evtl. mal
> COUNT(c.*) versuchen? *confused*
>

COUNT(c.*) lieferte eine Fehlermeldung, COUNT(c.company_id) ungefähr
das gleiche Ergebnis wie COUNT(*)

> >> SELECT
> >> cs.company_name, c.c
> >> FROM
> >> (SELECT
> >> company_id, COUNT(*) c
> >> FROM
> >> contacts
> >> GROUP BY
> >> company_id) c
> >> INNER JOIN
> >> companysites cs USING (company_id)
>
> > Diese Query war mit MySQL 5.0.45 deutlich schneller (unter 0.3s-0.5s),
> > selbst wenn alle Daten abgefragt wurden.

Hierzu auch nochmal der Explain:
-> contacts
-> GROUP BY
-> company_id) c
-> INNER JOIN
-> companysites cs USING (company_id) \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 117230
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: cs
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: c.company_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: contacts
type: index
possible_keys: NULL
key: company_id
key_len: 4
ref: NULL
rows: 174858
Extra: Using index
3 rows in set (0.66 sec)

> Na immerhin, 50% richtig, das reicht für ne 4, bin versetzt ;-)

Das war schon richtig gut ;-) ... d.h. eigentlich bin ich schon
zufrieden. Wenn sich noch eine weitere Optimierungsmöglichkeit findet,
umso besser.

Gruß

Thilo