Letztes Wochenende habe ich mich einige Stunden mit der Optimierung einer SQL-Abfrage herumgeschlagen, die eine beliebige Zahl zufälliger Datensätze aus einer Tabelle fischen soll und dabei mit JOINs aus zwei weiteren Tabellen die zugehörigen Daten ziehen. Mein erster Ansatz war dabei folgender:
SELECT an.frage_id AS frage_id,
an.antwort_text AS antwort_text,
an.timestamp AS timestamp,
fr.frage_text AS frage_text,
fr.timestamp AS fr_timestamp,
me.member_id AS member_id,
me.member_name AS member_name
FROM member_antworten an
JOIN member_fragen fr
ON fr.frage_id = an.frage_id
JOIN members me
ON an.member_id = me.member_id
WHERE an.antwort_text != '#'
ORDER BY RAND()
LIMIT 10
An sich eine zweckmäßige Query, die auch im Netz in den meisten Fällen so empfohlen wird. Nur: 5 Sekunden auf meinem lokalen Testsystem ist deutlich zu lang, zumal die gleiche Query ohne das "ORDER BY RAND" in 0,003 Sekunden durch ist. Also habe ich mich auf die Suche begeben.
Mein erster Ansatz bestand darin, die zufällige Bestimmung der Datensätze aus der Tabelle members_antworten über eine Subquery in der WHERE-Klausel zu erledigen, also lauteten die letzten drei Zeilen folgendermaßen:
/* anderer Kram… */
WHERE an.antwort_id IN (
SELECT antwort_id
FROM member_antworten
WHERE an.antwort_text != '#'
ORDER BY RAND()
LIMIT 10
)
Wer den Abschnitt zu RAND() im MySQL-Handbuch aufmerksam gelesen hat wird wissen was jetzt passiert: Nichts. Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.
Und das dauert bei 13000 Datensätzen eine Weile. Nach zehn Minuten hab ich also den mysql-Prozess neu gestartet. Mist.
ORDER BY RAND() bringt uns also nicht weiter, die Frage ist aber: Wie zur Hölle bekomme ich zufällige Datensätze in endlicher Zeit aus einer Datenbank. Einen Zufallswert in PHP erzeugen und in der Query übergeben kommt nicht in Frage, weil die Werte des Primärschlüssels durchaus Löcher haben können und die kennt diese Zufallszahl nicht. Dieser Artikel klang vielversprechend, dieser Folgeartikel sogar noch mehr. Kern dieser Ansätze ist eine zusätzliche Spalte mit Zufallswerten, die mit einem einmal pro Query erzeugten Zufallswert multipliziert werden und dann der Abstand dazu entscheidet, welche Datensätze selektiert werden. Interessanter Ansatz, nur mit 1,7 Sekunden noch immer recht langsam und irgendwie auch unsinnig kompliziert. Das müsste doch einfacher gehen, ich habe also weiter probiert und viel verworfen. Die Performance war immer gleich schlecht mit verschiedenen mehr oder weniger kreativen Ansätzen.
Schlussendlich bin ich bei dieser halbwegs eleganten Query gelandet, die mit 1,7 Sekunden auf etwa gleichem Niveau war:
SELECT an.frage_id AS frage_id,
an.antwort_text AS antwort_text,
an.timestamp AS timestamp,
fr.frage_text AS frage_text,
fr.timestamp AS fr_timestamp,
me.member_id AS member_id,
me.member_name AS member_name
FROM (
SELECT member_id,
frage_id,
antwort_text,
timestamp
FROM member_antworten
WHERE antwort_text != '#'
ORDER BY RAND()
LIMIT 10
) an
JOIN members me
ON me.member_id = an.member_id
JOIN member_fragen fr
ON fr.frage_id = an.frage_id
Je ein Index für Spalte member_id und frage_id haben das Ding schließlich auf 0,2 Sekunden gehoben, was passabel ist. Schön an dieser Lösung finde ich, dass nur noch die nötigen Datensätze gejoint werden und man nicht die Übersicht verliert.
Falls jemand das hier gelesen hat und mir einen zweckmäßigen Tipp geben kann, wie ich das offenbar langsame ORDER BY RAND() auch aus der Subquery wegoptimieren kann, soll er sich bitte hier melden. Bei der Gelegenheit könnte mir auch mal jemand erklären, warum es keine allgemeingültige und schnelle Möglichkeit gibt, zufällige Datensätze in SQL zu selektieren. Die ORDER BY RAND()-Kiste ist zum einen sehr langsam und zum anderen ein MySQL-Dialekt (genau wie das unglaublich praktische LIMIT).
Ein interessanter Ansatz bei hoher Last wäre ein Caching des Zufalls, also dass es eine Spalte mit Zufallswerten gibt, die nur auf Anfrage oder mit einem Timer neu geschrieben werden und sonst nur gelesen. Das wäre wirklich fix, aber wenn man schon Caches einsetzt, kann man auch gleich das Ergebnis der Query cachen und braucht nicht mal mit der Datenbank zu reden. Symfony bietet übrigens für solche Zwecke ein recht flexibles Cachesystem.
P.S.: Ach ja, die zugehörige Applikation ist ein Interviewsystem in einer internen kleinen Community und das DBMS ist MySQL5, falls das jemanden interessiert.
