Query-Performance

[Bruno]

Guten Tag,

folgender SQL-Query hatte vor kurzem den mySQL 5.0-Server lahm gelegt:

SELECT SQL_CALC_FOUND_ROWS tab1.col1, col2, col3 FROM tab1  
LEFT JOIN tab2 ON tab1.id=tab2.tab1_id  
LEFT JOIN tab3 ON tab2.id=tab3.tab2_id  
LEFT JOIN tab4 ON tab3.id=tab4.tab3_id  
WHERE  tab1.col1 LIKE 'foo'  
ORDER BY col1 ASC, col2 DESC, col3 ASC LIMIT 0,10

Meine Frage ist nun, ob ich die Performance von dem Befehl steigern kann.
tab1 ist eine Tabelle mit etwa 200.000 Datensätzen. Kann es sein, dass erst die ganzen Joins stattfinden und danach erst das WHERE ausgewertet wird? (In meinen Tests war das meiner Meinung nach nicht der Fall)

Mit freundlichen Grüßen,
Bruno

[Struppi]

Meine Frage ist nun, ob ich die Performance von dem Befehl steigern kann.

Sicher.

tab1 ist eine Tabelle mit etwa 200.000 Datensätzen. Kann es sein, dass erst die ganzen Joins stattfinden und danach erst das WHERE ausgewertet wird? (In meinen Tests war das meiner Meinung nach nicht der Fall)

Was für Test's? was sagt Explain?

Struppi.

[Bruno]

tab1 ist eine Tabelle mit etwa 200.000 Datensätzen. Kann es sein, dass erst die ganzen Joins stattfinden und danach erst das WHERE ausgewertet wird? (In meinen Tests war das meiner Meinung nach nicht der Fall)

Was für Test's? was sagt Explain?

Ignorieren wir mal meine Tests. Diese sind nichts sagend habe ich gerade festgestellt. :P

Ich hab mal den Explain durchgeführt:
phpMyAdmin liefert mir 4 Datensätze.
Der erste ist ein SIMPLE select type auf tab1 mit "using where" und "using filesort". Allerdings mit "type=ALL" und "possible_keys=NULL" :X Ich schätze dass man hier noch optimieren kann.
Die nächsten 3 Datensätze sind die Joins und haben alle einen Eintrag unter possible_keys (und zwar Unique-Spalten). Der type von diesen ist eq_ref.

Was ich nicht verstehe ist, warum tab1 "possible_keys=NULL" ist. Denn der WHERE-Vergleich findet auf einer unique-Spalte statt.

[Vinzenz Mai]

Hallo Bruno,

folgender SQL-Query hatte vor kurzem den mySQL 5.0-Server lahm gelegt:

dieser ganz bestimmt nicht. Wenn doch, überdenke Deine Bezeichner!
Sprich: Du hast das Problem fast bis zur Unkenntlichkeit verstümmelt. Das ist keine besonders gute Idee.

SELECT SQL_CALC_FOUND_ROWS tab1.col1, col2, col3 FROM tab1

-- aus welchen Tabellen sind col2 und col3?

LEFT JOIN tab2 ON tab1.id=tab2.tab1_id
LEFT JOIN tab3 ON tab2.id=tab3.tab2_id
LEFT JOIN tab4 ON tab3.id=tab4.tab3_id

-- warum dreimal LEFT JOIN? Alle aufeinander aufbauend. Da gibt es keine
-- Chance, die Auswertungsreihenfolge umzustellen. Bist Du Dir ganz sicher, -- dass Du keine INNER JOINs verwenden kannst?

WHERE  tab1.col1 LIKE 'foo'

-- ist der Mustervergleich wirklich nötig. Reichte nicht das Gleichheitszeichen?

ORDER BY col1 ASC, col2 DESC, col3 ASC LIMIT 0,10

  
\-- LIMIT kann gelegentlich tödlich für die Performance sein :-)  
\-- könnte für filesort verantwortlich sein.  
  
  
Freundliche Grüße  
  
Vinzenz

[Bruno]

dieser ganz bestimmt nicht. Wenn doch, überdenke Deine Bezeichner!

Mein Hoster meinte, dass durch diesen Query der mySQL-Server massiv belegt wurde, wodurch die Prozesse hängen geblieben sind und ich keine neuen Verbindungen herstellen konnte.
In einem Blog habe ich gelesen, dass SQL_CALC_FOUND_ROWS dazu geführt hat, dass bei demjenigen ebenfalls der SQL-Server in unregelmäßigen Abständen abgestürzt ist.

-- aus welchen Tabellen sind col2 und col3?

Die Spalten kommen aus den diversen Tabellen tab1 bis tab4. Eigentlich lese ich noch weitere Spalten aus, aber ich dachte die 3 reichen vorerst um das Problem zu schildern.

LEFT JOIN tab2 ON tab1.id=tab2.tab1_id
LEFT JOIN tab3 ON tab2.id=tab3.tab2_id
LEFT JOIN tab4 ON tab3.id=tab4.tab3_id

-- warum dreimal LEFT JOIN? Alle aufeinander aufbauend. Da gibt es keine
-- Chance, die Auswertungsreihenfolge umzustellen. Bist Du Dir ganz sicher, -- dass Du keine INNER JOINs verwenden kannst?

INNER JOINS funktionieren auch, allerdings habe ich keinen Unterschied (zumindest bzgl Geschwindigkeit) feststellen können.
Bezüglich der Auswertungsreihenfolge noch einmal den richtigen Query:

INNER JOIN tab2 ON tab1.id=tab2.tab1_id  
INNER JOIN tab3 ON tab1.time=tab3.time  
INNER JOIN tab4 ON tab1.group=tab4.id

WHERE  tab1.col1 LIKE 'foo'

-- ist der Mustervergleich wirklich nötig. Reichte nicht das Gleichheitszeichen?

Zur Zeit leider ja, da eigentlich mit '%foo%' verglichen wird. Das ist vermutlich der Performance-Killer. Das könnte ich vermutlich lösen, indem ich einen eigenen Index aufbaue oder den Volltext-Index nutze.

[Vinzenz Mai]

Hallo,

LEFT JOIN tab2 ON tab1.id=tab2.tab1_id
LEFT JOIN tab3 ON tab2.id=tab3.tab2_id
LEFT JOIN tab4 ON tab3.id=tab4.tab3_id

-- warum dreimal LEFT JOIN? Alle aufeinander aufbauend. Da gibt es keine
-- Chance, die Auswertungsreihenfolge umzustellen. Bist Du Dir ganz sicher, -- dass Du keine INNER JOINs verwenden kannst?

INNER JOINS funktionieren auch, allerdings habe ich keinen Unterschied (zumindest bzgl Geschwindigkeit) feststellen können.
Bezüglich der Auswertungsreihenfolge noch einmal den richtigen Query:

INNER JOIN tab2 ON tab1.id=tab2.tab1_id

INNER JOIN tab3 ON tab1.time=tab3.time
INNER JOIN tab4 ON tab1.group=tab4.id

  
Inner Joins haben ein normalerweise ein anderes Ergebnis als Outer Joins. Zusammenhängende INNER JOINS lassen sich in der Reihenfolge vertauschen, ohne das Ergebnis zu beeinflussen. Für OUTER JOINs gilt dies nicht.  
  
Bei Verwendung von INNER JOIN darf der Query Optimizer die Reihenfolge verändern, um einen besseren Ausführungsplan zu erhalten. Bei Verwendung von Outer Joins darf er dies nicht. Deswegen verwendet man Outer Joins nur dann, wenn man die zusätzlichen Ergebnisse, die ein Outer Join liefert, auch wirklich benötigt.  
  
  
Freundliche Grüße  
  
Vinzenz

[Bruno]

Bei Verwendung von INNER JOIN darf der Query Optimizer die Reihenfolge verändern, um einen besseren Ausführungsplan zu erhalten. Bei Verwendung von Outer Joins darf er dies nicht. Deswegen verwendet man Outer Joins nur dann, wenn man die zusätzlichen Ergebnisse, die ein Outer Join liefert, auch wirklich benötigt.

Ich muss mich korrigieren: Die INNER-JOINs sind in meinem Fall um ein Vielfaches langsamer! Mit den alten Left-Joins brauchte die Seite 0,7 sec bis das erste Byte geliefert wurde. Mit den Inner-Joins warte ich jetzt mehrere sec.
Merkwürdig ist das schon, denn lokal mit phpmyadmin getestet macht es zeitlich kaum einen Unterschied.

[Jonny 5]

Hi,

SQL_CALC_FOUND_ROWS

Das ist das erste Problem. Brauchst du das wirklich? Wenn es darum geht, zb zu berechnen, ob auf die aktuelle Seite eine weitere folgt: Zb hast du 10/p, dann limit 11. Wenn 11es ausgespuckt wird, gibts noch eine Seite.

Zur Zeit leider ja, da eigentlich mit '%foo%' verglichen wird. Das ist vermutlich der Performance-Killer. Das könnte ich vermutlich lösen, indem ich einen eigenen Index aufbaue oder den Volltext-Index nutze.

Genau, oder aber es reicht 'foo%' Wenn der Joker aber vorne steht, wird kein Index verwendet. Für ersteres muß natürlich ein Index auf der Spalte liegen, sonst wärs auch langsam.

Für die Joins dürften ja alle entsprechenden Spalten über Indexe verfügen? Dann sollt der Unterschied zwischen left/inner join auch nicht recht groß sein.

Schöne Grüße,
Jonny 5

[Bruno]

Das ist das erste Problem. Brauchst du das wirklich? Wenn es darum geht, zb zu berechnen, ob auf die aktuelle Seite eine weitere folgt: Zb hast du 10/p, dann limit 11. Wenn 11es ausgespuckt wird, gibts noch eine Seite.

So mache ich das derzeit als Workaround. Allerdings kann der User so immer nur eine Seite weitergehen.

Genau, oder aber es reicht 'foo%' Wenn der Joker aber vorne steht, wird kein Index verwendet.

Jau, also das geht natürlich verdammt schnell. Allerdings kann der zu suchende Begriff auch innerhalb eines Wortes stehen. Also bspw. existiert auch der Eintrag 'BarFoo', der entsprechend gefunden werden soll.
Gibt es evtl weitere interessante Infos zu LIKE in Verbindung mit Indizes?

[Jonny 5]

Hi nochmal,

Jau, also das geht natürlich verdammt schnell. Allerdings kann der zu suchende Begriff auch innerhalb eines Wortes stehen. Also bspw. existiert auch der Eintrag 'BarFoo', der entsprechend gefunden werden soll.

Gibt es evtl weitere interessante Infos zu LIKE in Verbindung mit Indizes?

Wenn er mittendrin steht ist es problematisch. Ansonsten, lässt sich in einer zweiten Spalte der umgekehrte String speichern. ZB  ooFraB, und es wird nach dem umgekehrten String gesucht, also zb 'ooF%'. Aber nicht sehr elegant.

Mit Boolescher Voltextsuche lässt sich zb nach Bar* suchen. Soweit ich weiß, aber nicht nach *arFo* Ansonsten match man mit der Volltextsuche volle Wörter.
Ich kann mich aber teuschen.

Viel Erfolg,
Jonny 5