spackblog

Gerade arbeite ich an einem mehr oder weniger zeitkritischen Projekt, das in Symfony entwickelt wird. Ich habe zwar das Symfony-Buch vor etwa einem Jahr gelesen (statt meine Thesis anzufangen), aber gearbeitet habe ich damit noch nie. Das ändert sich jetzt unter gewissem Druck. Morgen habe ich mittags einen Termin mit meinem Auftraggeber und bin noch nicht wirklich weit gediehen. Warum? Zwei lustige Probleme mit dem halbfertig angelieferten Datenmodell haben mich heute beinahe den ganzen Tag gekostet:

1. VARCHAR-Feldtypen brauchen (zumindest in MySQL) eine definierte Länge. Ich hatte die im Modell vorgegebenen Feldtypen einfach übernommen. Zwar hatte ich mich gewundert über die fehlende Längenangabe, aber hab mir nichts weiter dabei gedacht. Ahnungslos laufe ich also mit Propel los und schaffe es nicht, mein Modell damit umzusetzen, die Fehlermeldungen waren leider auch wenig hilfreich. Irgendwann bin ich dann drauf gekommen. Naja, hätte ich wissen müssen.

2. Man sollte Tabellen nicht order nennen! Um diese Erkenntnis zu erlangen, habe ich quasi den restlichen Tag gebraucht. Propel baut problemlos sein Modell damit auf und MySQL legt eine solche Tabelle auch ohne Murren an, nur scheitert jede Anfrage mit dem Tabellennamen irgendwo im SELECT mit einem SQL-Syntaxfehler. Eigentlich klar, ist ORDER doch ein SQL-Schlüsselwort, weswegen auch die Syntax-Hervorhebung von Notepad++ an solchem SQL-Code scheitert. Dieser Effekt hat mich letztlich auch auf die Lösung gebracht, als ich mir die von Propel generierte lib.model.schema.sql doch noch mal genauer angeguckt habe. Also flugs die Tabelle umbenannt, das Schema neu generiert und zack, das Adminmodul funktioniert endlich ohne Probleme.

Zu meiner Verteidigung muss ich anführen, dass beide Problemursachen nicht auf meinem Mist gewachsen sind, sondern schon mit meiner Modell-Vorlage ins Haus kamen, die allerdings ins Blaue hinein geschrieben waren und nicht validiert. Das habe ich leider gemerkt…

P.S. Symfony ist wirklich genial. Zwar habe ich heute nur mit dem Model-Layer gearbeitet, aber auch hier merkt man schon, wie ausgefeilt das ganze Framework ist. Es wird mir eine Freude sein, tiefer in die Entwicklung einzusteigen.

P.P.S. Sehr hilfreich für häufige Model-Rebuilds ist übrigend dieses Script, das einen Dump der Datenbank macht, das Modell neu aufbaut und die Daten danach wieder einspielt. Das Symfony-Standardverhalten bei einem model-build ist nämlich, alle Tabellen zu löschen und neu anzulegen, was leider die darin enthaltenen Daten entsorgt.

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.