Performance von Joins

Performance von Joins

am 30.09.2004 14:11:20 von Andreas Sakowski

Hallo,

ich habe eine Query mit folgender Struktur
(Namen der Übersicht wegen geändert) :

1)

select sql_calc_found_rows
a.wert as awert
, b.wert as bwert
, c.wert as cwert
, d.wert as dwert
, e.wert as ewert
, f.wert as fwert
, sum( g.wert ) as gwert
from a
left join b on b.id = a.bid
left join c on c.id = a.cid
left join d on d.id = a.did
left join e on e.id = a.eid
left join f on f.id = a.fid
left join g on g.id = a.gid
where
a.datum > now()
group by
a.datum
order by
a.datum
limit 0 , 20

In der Tabelle a sind ca. 50000 Einträge. Ein explain liefert
folgende Ausgabe:

table type possible_keys key key_len ref rows Extra
a range datum datum 3 NULL 28058 Using where;
Using temporary; Using filesort
b eq_ref PRIMARY PRIMARY 4 a.bid 1
c eq_ref PRIMARY PRIMARY 3 a.cid 1
d eq_ref PRIMARY PRIMARY 3 a.did 1
e eq_ref PRIMARY PRIMARY 3 a.eid 1
f eq_ref PRIMARY PRIMARY 3 a.fid 1
g eq_ref PRIMARY PRIMARY 3 a.gid 1

Für mich sind hier alle Indexe richtig gesetzt.

Die Query dauert ca. 7 Sekunden.

Wenn ich die ganzen Joins weglasse ergibt sich folgende Query:

2)

select sql_calc_found_rows
*
from a
where
a.datum > now()
group by
a.datum
order by
a.datum
limit 0 , 20

Diese Query dauert nur ca. 1/10 der Zeit, nämlich ca. 0,7 Sek.

Ich gehe davon aus, das mysql erst mal alle ca. 28000 Joins
ermittelt und dann halt nur die ersten 20 zurückschickt.
Da die anderen Tabellen die Auswahl der Datensätze nicht
beeinflussen, ist dies aus meiner Sicht eigentlich nicht
nötig. Es könnte vielmehr erst zum Schluss, wenn die
20 Zeilen zurückgegeben werden, der Join ausgeführt werden.

Kann man das irgendwie optimieren oder muss ich erst
die unter 2) dargestellte Query absetzen und mir dann die
weiteren Daten dann mit jeweils einer eigenen Abfrage
holen?

Gruß
Andreas Sakowski

Re: Performance von Joins

am 30.09.2004 17:37:41 von Stefan Kilp

Andreas Sakowski schrieb:

[...]
leg mal ein index aufs datum und versuchs noch mal.

gruss
stefan

Re: Performance von Joins

am 30.09.2004 17:58:56 von Andreas Sakowski

Stefan Kilp schrieb:
> leg mal ein index aufs datum und versuchs noch mal.

Da ist bereits in Index drauf. Und der wird gemäß explain
ja auch verwendet. Im Testbeispiel hat der Index die
Datenmenge ja auch von ca. 50.000 auf ca. 28.000
verringert. Aber es sind halt immer noch 28.000
zutreffde Zeilen von denen ich aber nur einen Ausschnitt
haben möchte.

Gruß
Andreas Sakowski

Re: Performance von Joins

am 04.10.2004 16:55:05 von Andreas Sakowski

Hallo nochmal,

hier noch ein weiteres Beispiel für einen langsamen join:

SELECT b.id, b.dateiname, g.belegid,
IF (
g.id IS NULL , b.id *1000000, g.gehoertzu *1000000 + g.id
) AS sort
FROM belege b
LEFT JOIN beleggruppen g ON g.belegid = b.id
LEFT JOIN belege b2 ON b2.id = g.gehoertzu
WHERE b.belegkreisnummer IS NULL
AND b2.belegkreisnummer IS NULL
ORDER BY sort

Diese Query benötigt ungefähr 4 Sekunden

Wobei folgende Query (berechnetes Feld sort und order by
weggenommen)

SELECT b.id, b.dateiname, g.belegid
FROM belege b
LEFT JOIN beleggruppen g ON g.belegid = b.id
LEFT JOIN belege b2 ON b2.id = g.gehoertzu
WHERE b.belegkreisnummer IS NULL
AND b2.belegkreisnummer IS NULL

weniger als 0,1 Sekunden benötigt.

In der Tabelle belege sind ca. 1500 Einträge, in der Tabelle
beleggruppen
sind ca. 500 Einträge. Als Ergebnis kommen ca. 20 Einträge heraus.
Das Feld sort beeinflusst in keiner Weise die Menge der Datensätze
und könnte zum Schluß auf die aussortierten Datensätze losgelassen
werden.

Wenn ich die Query ohne where-Bedingungen loslasse benötigt
die Query sogar über 20 Sekunden.

Was kann ich tun?

Ich danke für alle Ratschläge.

Gruß
Andreas Sakowski


P.S. hier noch die explains

für die beiden ersten Queries :
b ref beleg beleg 5 const 561 Using where; Using temporary; Using
filesort
g eq_ref PRIMARY PRIMARY 4 b.id 1
b2 eq_ref PRIMARY PRIMARY 4 g.gehoertzu 1 Using where

für das Query ohne where-Bedingung
b ALL NULL NULL NULL NULL 1207 Using temporary; Using filesort
g eq_ref PRIMARY PRIMARY 4 b.id 1
b2 eq_ref PRIMARY PRIMARY 4 g.gehoertzu 1

Re: Performance von Joins

am 04.10.2004 17:27:59 von Christian Kirsch

Andreas Sakowski wrote:

> Hallo nochmal,
>
> hier noch ein weiteres Beispiel für einen langsamen join:
>
> SELECT b.id, b.dateiname, g.belegid,
> IF (
> g.id IS NULL , b.id *1000000, g.gehoertzu *1000000 + g.id
> ) AS sort
> FROM belege b
> LEFT JOIN beleggruppen g ON g.belegid = b.id
> LEFT JOIN belege b2 ON b2.id = g.gehoertzu
> WHERE b.belegkreisnummer IS NULL
> AND b2.belegkreisnummer IS NULL
> ORDER BY sort
>
> Diese Query benötigt ungefähr 4 Sekunden
>
> Wobei folgende Query (berechnetes Feld sort und order by
> weggenommen)
>
> SELECT b.id, b.dateiname, g.belegid
> FROM belege b
> LEFT JOIN beleggruppen g ON g.belegid = b.id
> LEFT JOIN belege b2 ON b2.id = g.gehoertzu
> WHERE b.belegkreisnummer IS NULL
> AND b2.belegkreisnummer IS NULL
>
> weniger als 0,1 Sekunden benötigt.

Allerdings holst Du auch mindestens zwei Felder weniger aus der DB (g.id,
g.gehoertzu). Ich *vermute*, dass dein Sort die Zeit frisst.

Wie sieht denn das Explain für das zweite Statement aus?


--
Christian Kirsch

Re: Performance von Joins

am 04.10.2004 17:53:01 von Andreas Sakowski

"Christian Kirsch schrieb> Andreas Sakowski wrote:

> Allerdings holst Du auch mindestens zwei Felder weniger aus der DB
> (g.id,
> g.gehoertzu). Ich *vermute*, dass dein Sort die Zeit frisst.

SELECT b.id, b.dateiname, g.belegid, g.id, g.gehoertzu
FROM belege b
LEFT JOIN beleggruppen g ON g.belegid = b.id
LEFT JOIN belege b2 ON b2.id = g.gehoertzu
WHERE b.belegkreisnummer IS NULL
AND b2.belegkreisnummer IS NULL

benötigt auch unter 0,1 Sekunden

> Wie sieht denn das Explain für das zweite Statement aus?

In meinem ersten Post war ich fälschlicherweise davon ausgegangen,
das die ersten beiden Queries den gleichen Explain liefern. Richtig
ist jedoch,
das die zweite Query bei der ersten Tabelle kein 'Using temporary' und
kein
'Using filesort' enthält.

b ref beleg beleg 5 const 561 Using where
g eq_ref PRIMARY PRIMARY 4 b.id 1
b2 eq_ref PRIMARY PRIMARY 4 g.gehoertzu 1 Using where

Das 'Using temporary' scheint also das Problem zu sein. Das ist
anscheinend
der Indikator dafür, das erst einmal alle Kombinationen ermittelt
werden
um dann die 20 richtigen Zeilen zu nehmen. Kann man das irgendwie
umgehen oder muss ich dafür dann einzelne Queries absetzen?

Gruß und danke
Andreas Sakowski

Re: Performance von Joins

am 05.10.2004 17:32:35 von Andreas Sakowski

Hallo.

Ich habe nochmal in der Doku nachgelesen und gelesen das das filesort
ab
mysql 4.1 verbessert wurde. Ich habe mir daher mal die MySQL
5.0.1-alpha
installiert und getestet:

Query 1:

SELECT b.id, b.dateiname, g.belegid,
IF (
g.id IS NULL , b.id *1000000, g.gehoertzu *1000000 + g.id
) AS sort
FROM belege b
LEFT JOIN beleggruppen g ON g.belegid = b.id
LEFT JOIN belege b2 ON b2.id = g.gehoertzu
WHERE b.belegkreisnummer IS NULL
AND b2.belegkreisnummer IS NULL
ORDER BY sort

Query 2:

SELECT b.id, b.dateiname, g.belegid,
IF (
g.id IS NULL , b.id *1000000, g.gehoertzu *1000000 + g.id
) AS sort
FROM belege b
LEFT JOIN beleggruppen g ON g.belegid = b.id
LEFT JOIN belege b2 ON b2.id = g.gehoertzu
WHERE b.belegkreisnummer IS NULL
AND b2.belegkreisnummer IS NULL

Query 2 unterscheidet sich nur in dem fehlenden Sort.


mysql 4.0.21 :

Query 1 dauert ca. 7 Sekunden

explain:

b ref beleg beleg 5 const 565 Using where; Using temporary; Using
filesort
g eq_ref PRIMARY PRIMARY 4 b.id 1
b2 eq_ref PRIMARY PRIMARY 4 g.gehoertzu 1 Using where

Query 2 dauert ca. 0,2 Sekunden

explain:

b ref beleg beleg 5 const 565 Using where
g eq_ref PRIMARY PRIMARY 4 b.id 1
b2 eq_ref PRIMARY PRIMARY 4 g.gehoertzu 1 Using where


MySQL 5.0.1-alpha :

Query1 dauert unter 0,1 Sekunden

explain:

1 SIMPLE b ref beleg beleg 5 const 565 Using where; Using temporary;
Using filesort
1 SIMPLE g eq_ref PRIMARY PRIMARY 4 fibu_00022_00002.b.id 1
1 SIMPLE b2 eq_ref PRIMARY PRIMARY 4 fibu_00022_00002.g.gehoertzu 1
Using where

Query 2 dauert unter 0,1 Sekunden

explain:

1 SIMPLE b ref beleg beleg 5 const 565 Using where
1 SIMPLE g eq_ref PRIMARY PRIMARY 4 fibu_00022_00002.b.id 1
1 SIMPLE b2 eq_ref PRIMARY PRIMARY 4 fibu_00022_00002.g.gehoertzu 1
Using where


Die Version 4.1 habe ich nicht installiert. Aber da in der Doku die
Veränderungen ab 4.1 erwähnt
sind gehe ich davon aus das die Verbesserung dort auch schon vorhanden
ist. Nun heißt es zu
überlegen was ich machen soll. Warten und hoffen das die Version 4.1
bald kommt oder doch
alle Queries noch mal überarbeiten und in einzelne Queries auflösen.

Falls jemand doch noch einen guten Ratschlag hat so würde ich mich
über eine Antwort freuen.


Gruß
Andreas Sakowski