MySQL

Ich habe mich in letzter Zeit ein klein wenig mit der Optimierung von MySQL-Datenbanken (besonders InnoDB) befasst. Mehr denn je wird einem dabei bewußt, was man eigentlich garnicht weiß und noch alles alles lernen könnte.

Für mich neu war z.B. das Wissen, dass man einem SELECT-Query explizit mitteilen kann, welchen Index es nutzen soll. (Gleiches gilt wohl auch für UPDATE-Querys.) Nicht immer ist der von MySQL selber ausgewählte Index der optimale.

Um die genutzten Indizes bei einem Query angezeigt zu bekommen, nutzt man EXPLAIN query (z.B. EXPLAIN SELECT a, b FROM table1 WHERE x=2 AND y=5).

Was vielleicht auch nicht alle wissen: Bei einem Join über mehrere Tabellen ist MySQL viel langsamer, wenn die zu vergleichenden Werte aus beiden Tabellen unterschiedliche Datentypen haben. Das trifft auch auf gleiche Typen mit unterschiedlicher Länge zu! So ist eine Abfrage mit Datentyp int(10) gejoint mit int(11) viel langsamer, als wenn beide Datentypen int(10) sind.

Noch viel viel mehr Tipps gibt es in dem sehr guten englischsprachigen Blog MySQL Performance Blog.

So ne Datenbank ist schon was tolles. Man stellt seine Abfrage und bekommt die richtigen Daten zurück (wenn die Abfrage richtig war...). Ganze einfache Geschichte.
$ergebnis = mysql_query("select a1, a2, a3 from Test");
if (@mysql_num_rows($ergebnis)>0)
{
while ($zeile=mysql_fetch_row($ergebnis))
{
// Mache was mit dem Ergebnis
}
}
else
{
// Fehlermeldung weil keine Ergebnise
}
Nachdem man mit diesem Code durch ist, hat man alle Ergebnisse einmal durchlaufen.

Manchmal ist es aber nötig, die Ergebnisse noch ein zweites Mal zu durchlaufen. Z.B. weil man in einer PHP-Datei die Daten an zwei verschiedenen Stellen auswerten muss und es sehr umständlich wäre, alle Auswertungen gleich an der ersten Stelle zu machen. Die naheliegenste Lösung wäre, die Abfrage einfach nochmals auszuführen. Das geht natürlich. Ist aber nicht wirklich effektiv, weil die Abfrage ja schonmal ausgeführt wurde. Warum genau das Gleiche nochmal machen?

Eine bessere Lösung ist die, das Ergebnis-Set so zurückzusetzen, dass man nochmal von vorne Anfangen kann. PHP bietet diese Möglichkeit mit dem Befehl mysql_data_seek(). Damit kann man den Zeiger im Ergebnis-Set an eine beliebige Stelle - auch an den Anfang - setzen. Der Anfang lautet dabei nicht 1, sondern 0!
mysql_data_seek($ergebnis, 0);
Jetzt kann ich mein Ergebnis-Set $ergebnis nochmal mit der While-Schleife durchlaufen.

Wie kann ich unter MySQL nach dem Zufallsprinzip mir eine Zeile auswählen lassen?

Das ganze funktioniert über die Sortierung und die Angabe von LIMIT:

SELECT ... FROM ... ORDER BY rand() LIMIT 1

rand() sortiert dabei die Tabelle nach dem Zufallsprinzip und LIMIT 1 liefert mir das erste Ergebnis. Ich kann natürlich auch einen anderen Wert für Limit einsetzen, um mehr als ein Ergebnis zu bekommen (oder Limit ganz weglassen, um alle Werte zufällig sortiert zu bekommen).

Mal wieder was gefunden, wo ich eine Weile dran zu knobeln hatte.

Das Problem war folgendes: Ich habe eine Tabelle, die für jeden Tag und jedes Rätsel (nennen wir es mal so) angibt, wie oft versucht wurde, das Rätsel zu lösen und wie oft das geschafft wurde. Also die Tabelle ist so aufgebaut:

raetsel (id as int, tag as date, anzahl as int, richtig as int, raetsel_id as int)

Jetzt möchte ich die fünf schwersten Rätsel des letzten Monats ermitteln. Sortiert nach dem Schweregrad und auch nur die Rätsel, die mindestens 10 mal im letzten Monat versucht wurden. Die Schwere eines Rätsels ergibt sich aus den Prozenten von anzahl und richtig (richtig/anzahl * 100).

Wie gesagt, in der Tabelle steht für jeden Tag eine Zeile. Ich möchte es aber für einen Zeitraum (z.B. einen Monat) ermitteln.

Also losgelegt und eine SQL-Abfrage erstellt:
SELECT raetsel_id, SUM(anzahl) AS gesamt, SUM(richtig) AS korrekt, korrekt/gesamt*100 AS prozent FROM raetsel WHERE tag >= '2007.01.01' AND tag <= '2007.01.31' GROUP BY raetsel_id ORDER BY prozent ASC LIMIT 5

Und was sicherlich nicht schwer zu erraten ist. Das ganze liefert eine Fehlermeldung. Er kennt keine Tabellen-Spalten namens korrekt und gesamt, mit denen ich ja in der Abfrage rechnen will. Jetzt könnte man die Berechnung weglassen (und damit auch die ORDER BY-Klausel) und würde Ergebnisse bekommen. Allerdings brauch ich sie ja nach der Schwere sortiert.

Um einfach an alle aggregierten Daten des Monats zu kommen würde folgende Abfrage ausreichen:
SELECT raetsel_id, SUM(anzahl) AS gesamt, SUM(richtig) AS korrekt from raetsel WHERE tag >= '2007.01.01' AND tag <= '2007.01.31' GROUP BY raetsel_id

Ich könnte jetzt die Berechnungen und die Sortierung nach der Schwere in der Anwendungs-Software machen. Aber wenn das z.B. auf einer Webseite mit PHP laufen soll, dann ist das nicht wirklich toll. Und MySQL kann das selber machen indem man eine Unterabfrage nutzt. Ich lasse über die Ergebnisse der obrigen Abfrage eine zweite Abfrage laufen, die mir die gewünschten Daten zurückliefert. Das ganze sieht dann folgendermaßen aus:

SELECT raetsel_id, gesamt, korrekt, korrekt/gesamt*100 AS prozent from (
SELECT raetsel_id, SUM(anzahl) AS gesamt, SUM(richtig) AS korrekt from raetsel WHERE tag >= '2007.01.01' AND tag <= '2007.01.31' GROUP BY raetsel_id) AS temp
WHERE gesamt > 9 ORDER BY prozent ASC LIMIT 5

Die Unterabfrage, die schon etwas weiter oben steht, ist kursiv. Diese Abfrage liefert mir ja die Summe aller Aufrufe und richtigen Lösungen für jedes Rätsel über den gesamten Zeitraum. Mit diesen Angaben kann ich in der äußeren Abfrage jetzt Rechnen und auch nach dieser Berechnung sortieren und mir so nur die besten 5 Rätsel zurückgeben lassen.