Performance-Optimierungen in einem Forum

mgutt hat geschrieben:

bloddy newbie hat geschrieben:Wie schaut die Abfrage aus?

Das ist die Abfrage:

Code: Alles auswählen

SELECT *
FROM topics
WHERE moved_id = 0
AND host_id = 1
ORDER BY last_time DESC
LIMIT 10

moved_id schließt Kopien des topics aus. host_id bestimmt die Domain auf der das topic veröffentlicht wurde (Multihosting) und last_time ist eben die Sortierung nach Aktualität. Indexe liegen auf moved_id, host_id und last_time. MySQL nutzt an dieser Stelle host_id. Getestet habe ich auch mehrspaltige, aber das Problem ist hier einfach die Anzahl der Zeilen aus dem WHERE-Teil, der dann filesort richtig zum arbeiten bringt (ab 100.000 Zeilen beginnt das Problem).

Was spricht gegen einen Index auf den Spalten moved_id, host_id? Das würde schnellstmöglich alle gewünschten Zeilen liefern, die mysql, dann noch nett sortiert zurückgibt. Oder meinst du es so, dass du so viele Zeilen durch den Index zwar schnell zurückbekommst, die Sortierung aber so lange dauert?
Warum funktioniert dann ein Index über die drei Spalten nicht? Hier müssen natürlich die where-Spalten am Anfang und die order by als letzte stehen. Aber eigentlich müßte MySQL dann sich die Daten perfekt sortiert aus dem Index holen können.

xAaron hat geschrieben:Was spricht gegen einen Index auf den Spalten moved_id, host_id? Das würde schnellstmöglich alle gewünschten Zeilen liefern, die mysql, dann noch nett sortiert zurückgibt. Oder meinst du es so, dass du so viele Zeilen durch den Index zwar schnell zurückbekommst, die Sortierung aber so lange dauert?

Genau. Die Menge an Ergebnissen ist das Problem. Nutze ich nur WHERE oder nur ORDER BY, dann ist die Abfrage schnell.

Warum funktioniert dann ein Index über die drei Spalten nicht? Hier müssen natürlich die where-Spalten am Anfang und die order by als letzte stehen. Aber eigentlich müßte MySQL dann sich die Daten perfekt sortiert aus dem Index holen können.

Die Frage ist, ob das nicht eher nachteilige Auswirkungen hat. Einmal dürfte das ein ziemlich großer Index werden. Und dann werden die last_time Spalte ja sehr häufig aktualisiert, so dass sich das UPDATE danach entsprechend verlangsamen dürfte.

Weiterhin funktioniert ein Index über drei Spalten nicht. Wenn ich den Index auf moved_id, host_id und last_time setze, ist die Abfrage genauso langsam wie vorher auch. Er nutzt dann host_id als Index.

Den 3-Spalten Index nutzt er nur, wenn ich zusätzlich last_time in den WHERE-Part übernehme (wie jetzt in meiner letzten Idee geäußert).

Außer ich lösche den einzelnen Index auf host_id. Dann nutzt er den 3-Spalten-Index und dann ist die Abfrage auch wieder schnell. Allerdings weiß ich leider gerade gar nicht, ob ich dadurch nicht ein neues Problem verursache, da ich ja nicht immer nach last_time vergleiche, sondern auch Sonderformen nutze (vorwärts, andere WHERE-Bedingungen, usw.).

EDIT:
Ok, ich denke ich weiß wo das Problem ist. Ich wusste nicht, dass die Reihenfolge der Spaltennamen im mehrspaltigen Index so wichtig ist. Werde das mal durchtesten.

Also der Tipp mit der Reihenfolge war wirklich gut. Nur durch drehen verschiedener WHERE-Parameter, konnte ich den Index überhaupt erst "aktivieren".

Trotzdem hänge ich noch an einem Problem. z.B. diese Abfrage ist schnell: Weil sie den Index host_forum_type nutzt, der über host, forum und type definiert wurde.

Dafür ist diese wiederrum langsam: Ich habe sowohl mit einzelnen Indexen auf forum getestet, also auch mit einem auf host und forum. Sobald ich diese setze, nutzt er diese zwar, statt host_forum_type, aber die Abfrage bleibt trotzdem lahm

Natürlich muss ich hier zusehen, dass ich von dem COUNT() wegkomme und diesen als Wert in der Tabelle "forums" hinterlege, aber trotzdem brauche ich eine Verbesserung, da ich danach die topics mit einem nahezu identischen Query auslese.