Order BY Performance Killer

[schmalle]

Ich brauche mal einen SQL Optimierer

Ich habe da eine Query um Kundendaten und diverses anderes zeug auszulesen. Der Anwender kann einige Parameter selbst einstellen. Unter anderem auch das Sortieren. Und genau das macht mit Sorgen. Es befinden sich ca. 1500 Datensätze in der ersten Tabelle.

SELECT * , o.FK_USER AS byuser
FROM tr_kunde k
LEFT JOIN tr_EGAL g ON k.FK_GROUP = g.ID_GROUP
LEFT JOIN tr_auchegal s ON k.ID_KUNDE = s.FK_KUNDE
LEFT JOIN tr_ganzegal o ON o.FK = k.ID_KUNDE AND o.FK_TYP = 'KUNDE'
LEFT JOIN tr_wasanderes ac ON k.ID_KUNDE = ac.FK AND ac.FK_TYP = 'kunde'
LEFT JOIN tr_auchschoen u ON k.FK_USER_M = u.ID_USER
LEFT JOIN tr_einetabelle r ON u.FK_ROOT = r.ID_ROOT
WHERE (
g.GROUP_LFT >=9 AND g.GROUP_RGT <=16
) AND (
r.ROOT_LFT >=1 AND r.ROOT_RGT <=22
)LIMIT 0 , 10

Läuft: 0.02 Sek.

Mit Sortierung wird´s dann richtig schön langsam.

SELECT *, o.FK_USER AS byuser
FROM tr_kunde k
LEFT JOIN tr_EGAL g ON k.FK_GROUP = g.ID_GROUP
LEFT JOIN tr_auchegal s ON k.ID_KUNDE = s.FK_KUNDE
LEFT JOIN tr_ganzegal o ON o.FK = k.ID_KUNDE AND o.FK_TYP = 'KUNDE'
LEFT JOIN tr_wasanderes ac ON k.ID_KUNDE = ac.FK AND ac.FK_TYP = 'kunde'
LEFT JOIN tr_auchschoen u ON k.FK_USER_M = u.ID_USER
LEFT JOIN tr_einetabelle r ON u.FK_ROOT = r.ID_ROOT
WHERE (
g.GROUP_LFT >=9 AND g.GROUP_RGT <=16
) AND (
r.ROOT_LFT >=1 AND r.ROOT_RGT <=22
)LIMIT 0 , 10 ORDER BY NACHNAME ASC , VORNAME ASC

Läuft 0.26 sek.

Explain() sagt mir beim ORDER BY, dass eine TEMP DB verwendet wird. Und die wird wohl auch die Performance fressen ... Hat jemand eine Idee, wie man das ganze beschleunigen kann?

Order by über ein INT Feld ist ebenso langsam. Das Setzen von weiteren Indizies auf die zu sortierenden Felder bringt 0,gar nichts
An den anderen Indizies habe ich schon ewig und drei tage gebastelt. Die scheinen so richtig zu sitzen.

Danke im voraus!

EDIT:

---

alle ID_ und FK_ Felder sind BIGINT UNSIGNED NOT NULL

---

[Quetschi]

Hmm... die Indizies werden beim sortieren wohl nix mehr bringen weil meines Wissens nach bei Abfragen mit Joins Mysql die Indizies meist (oder sogar gar nicht?) verwenden kann - korrigiert mich bitte wenn ich da falsch liege - aber ich glaub das so mal in nem Buch gelesen zu haben, das ich aber leider grad ned zur Hand hab.

Probehalber:
Wie lange läuft das ganze, wenn du erst die Abfrage ohne ORDER BY laufen lässt und mit dem Ergebnis selbst eine temporary heap tabelle bildest und diese dann erst mit ORDER BY abfrägst?? Wäre ja zumindest leicht das mal kurz zu probieren...

[heddesheimer]

Gehört das LIMIT nicht ans Ende der Abfrage?

Das Problem dürfte die Sortierung nach zwei Feldern sein. Nur nach Vorname oder Nachname dürfte schneller gehen, wenn du jeweils einen Index dafür angelegt hast?

Wenn du beide Felder zur Sortierung brauchst, lege mal einen kombinierten Index aus beiden Feld an. Den kann MySQL dann auch für die Sortierung optimal nutzen.

Ich hatte jedenfalls mit sortierten Daten noch nie Probleme, zumal 0.26 Sekunden nicht wirklich lang sind. Bei einer meiner Kunden-Datenbanken habe ich 500000 Adressen und komme auf 1.7 Sekunden. Wenn ich einen passenden Index zusammensetze, sind es auch nur 0.06 Sekunden.

Gruß Marian

[schmalle]

Danke für die Antworten.
Die Lösung war leider nicht dabei.

@ heddesheimer Limit wurde wohl beim C&P an die falsche Stelle gesetzt :-) *wurstfinger* natürlich gehört es ans Ende.

Das Problem dürfte die Sortierung nach zwei Feldern sein. Nur nach Vorname oder Nachname dürfte schneller gehen, wenn du jeweils einen Index dafür angelegt hast?

Stimmt nicht, wie ich oeben schon geschrieben hab.

zumal 0.26 Sekunden nicht wirklich lang sind.

Natürlich ist das lang.

Die Lösung des Problems ist mir dennoch gelungen. Ich ahbe mir eine Art Ergebnis Cache gebastelt.

[asp2php]

Schmalli, schon mal probiert

.... order by tblAlias.colName ...

denn sonst muss sich das DBMS zuerst prüfen, ob der Name eindeutig ist

[schmalle]

@asp Gute Idee, leider nicht das gewünschte Ergebnis. Die Zeitersparnis ist kaum messbar. Aber egal. jetzt die Query nur einmal ausgeführt, danach wird nur noch der Cache gelesen.