Sie sind hier : sebastian1012.bplaced.net/ homepage-neu / kreuz-und-quer / tutorials-info-neuigkeiten-php / mit-mysql-zufaelligen-datensatz-selektieren.php

Mit MySQL zufälligen Datensatz selektieren

Ich habe ja bereits zwei mal Beiträge geschrieben, wie man performant einen bzw. mehrere Datensätze per SQL selektieren kann. Heute möchte ich einen neuen, eleganteren Ansatz beschreiben, der allerdings etwas PHP benötigt (die anderen basierten allein auf MySQL).

Um eine Einleitung zu finden, möchte ich nochmal betonen, dass folgende Abfrage sehr schlecht ist:

SELECT spalte  FROM tabelle  WHERE andereSpalte='123'  ORDER BY RAND() LIMIT 1

Diese Möglichkeit sieht zwar im ersten Augenblick elegant aus und funktioniert ja auch, allerdings ist sie hochgradig langsam! Und mit langsam meine ich seeeehr langsam!
Das Problem bei dieser Abfrage ist, dass für jeden Datensatz, der die Bedingungen erfüllt (wenn man keine WHERE-Bedingung hat, wird es nur noch schlimmer) ein Zufallswert bestimmt werden muss und erst anschließend nach diesem Wert sortiert werden kann. Dazu muss dann noch eine temporäre Tabelle erstellt werden. Die entsprechende EXPLAIN-Ausgabe (von mir leicht gekürzt, da sonst zu breit) sieht dann beispielsweise so aus:

select_type table type possible_keys key ref rows Extra
SIMPLE tabelle ref andere andere const 5870 Using temporary; Using filesort

Insbesondere die letzte Spalte ist hier interessant: Es muss eine temporäre Tabelle erzeugt werden (Using temporary) anschließend sortiert werden (Using filesort – dazu kann natürlich kein Index benutzt werden). In den Query-Cache kann eine solche Abfrage natürlich auch nicht gespeichert werden, da die RAND()-Funktion ja bei jedem Aufruf andere Datensätze auswählt.

Nun kommt die Alternative, auf die mich ein sehr interessanter Beitrag von Tutsplus.com mit dem Titel Top 20+ MySQL Best Practices gebracht hat. Dazu sind 2 Querys und ein wenig PHP-Code nötig.
Und zwar selektiert man erstmal die Anzahl aller Datensätze, die die Bedingungen erfüllen. Dazu kann (eigentlich) immer ein Index genutzt werden:

SELECT COUNT(*) FROM tabelle WHERE andereSpalte='123'

Mit dieser Anzahl kann nun mit PHP eine Zufallszahl zwischen 0 und (Anzahl-1) gebildet werden:

$rs= mysql_query("SELECT COUNT(*)  FROM tabelle  WHERE andereSpalte='123'"); $anzID = mysql_result($rs,0); $rand = mt_rand(0,$anzID - 1);

Und nun kommt das Interessante. Diese Zufallszahl kann nun das OFFSET für die eigentliche Nutzdaten-Query werden:

$rs = mysql_query("SELECT spalte  FROM anzeigen  WHERE andereSpalte='123'  LIMIT ".$rand.",1");

Und das ist das ganze Geheimnis. Nun kann immer der Index für die Bedingung genutzt werden und dann wird einfach der x-te Datensatz zurückgeliefert, der die Bedingung erfüllt. Im EXPLAIN sieht das ganze dann so aus:

select_type table type possible_keys key ref rows Extra
SIMPLE tabelle ref andere andere const 5870  

Man beachte, dass in der Spalte Extra nichts steht – eine optimale Abfrage.
Wie groß der Geschwindigkeitsunterschied in der Praxis ist, hängt von der Anzahl der Datensätze ab, die die Bedingungen erfüllen. Je mehr Datensätze, desto langsamer ist ORDER BY RAND(). Auf jeden Fall spart man sich aber die temporäre Tabelle und das ist schon viel wert.

In meiner Testdatenbank erfüllten ca. 6000 von insgesamt ca. 500.000 Datensätzen die Bedingung andereSpalte=’123′.
Die Abfrage mit ORDER BY RAND() LIMIT 1 brauchte 0,05 Sekunden.
Die Abfragen (inkl. PHP) mit der oben beschriebenen Art und Weise mit COUNT(*) und LIMIT $rand,1 benötigte 0,001 Sekunden.
Das bedeutet eine Beschleunigung um den Faktor 50.

Nun möchte man aber manchmal mehrere Datensätze zufällig selektieren und nicht nur einen. Hierzu ist die Vorgehensweise sehr ähnlich, nur der PHP-Code muss etwas erweitert werden.
Zuerst wird wieder die Gesamtanzahl an Datensätzen bestimmt, die die Bedingungen erfüllt (siehe oben).
Anschließend müssen x Zufallszahlen gebildet werden. Und mit diesen wird dann eine SQL-Abfrage mit UNIONs gebaut. Im Code sieht das so aus:

$einlesen = mysql_query("SELECT COUNT(*)  FROM tabelle WHERE  andereSpalte='123'"); $anz_cnt = mysql_result($einlesen,0); $rands = array(); $x = 3; while(count($rands)<$x && $anz_cnt>count($rands)) {   $rand = mt_rand(0,$anz_cnt - 1);   if(!isset($rands[$rand])) $rands[$rand] = $rand; }   $queryparts = array(); foreach($rands as $rand) $queryparts[] = "SELECT spalte  FROM tabelle  WHERE andereSpalte='123'  LIMIT ".$rand.",1";   $rs = mysql_query("(".implode(") UNION ALL (",$queryparts).")");

Die Schleife beim Erhalten der Zufallszahlen ist deshalb eine while- und keine for-Schleife, weil es sonst passieren kann, dass es zwar mehr als x Datensätze gibt, die die Bedingungen erfüllen, aber dummerweise 2 mal die gleiche Zufallszahl ermittelt wird. Die Bedingung $anz_cnt>count($rands) dient dazu, dass keine Endlosschleife entsteht, wenn weniger als x Datensätze die Bedingung erfüllen.
Bei der abschließenden Abfrage wird UNION ALL benutzt statt UNION, damit MySQL die Einzelergebnisse nicht noch versucht zu gruppieren (wir wissen ja durch die while-Schleife bereits, dass keine Duplikate selektiert werden können). UNION bedeutet nämlich in Wirklichkeit UNION DISTINCT.
Auch hier bleibt die Spalte Extra beim EXPLAIN leer.

Wie findet ihr diesen Ansatz? Habt ihr noch andere Vorschläge oder Vorschläge, wie man diese Vorgehensweise noch optimieren könnte? Ich freue mich auf Kommentare!