Performance von MySQL’s ORDER BY RAND() optimieren

Die Performance von MySQL's ORDER BY RAND() ist bei großen Tabellen nicht gut. Das liegt an der Tatsache, dass ORDER BY RAND() die komplette Tabelle zufällig sortiert. Eine Sortierung auf einer großen Tabelle ist ein Performance-Killer.

Wir gehen in den folgenden Code-Beispielen davon aus, dass wir zufällige Daten der Anzahl $NUM_OF_RESULTS aus einer Tabelle table holen wollen. Wir gehen weiter davon aus, dass nicht alle Daten der Tabelle relevant sind. Deshalb schränken wir unsere Ergebnismenge durch eine Where-Klausel where ein. Der Datenbankzugriff erfolgt über PHP und PDO.

Gewöhnlicher Zugriff mit ORDER BY RAND()

Folgend der Zugriff mit ORDER BY RAND().

$query = $conn->query('SELECT * FROM table WHERE where ORDER BY RAND() LIMIT 0,'.$NUM_OF_RESULTS);

if ($query->rowCount() > 0) {
    $result = $query->fetchAll();
    foreach ($result as $row) {
        echo $row[0].'<br />';
    }
}

Mehode 1: Zufällige Ergebnisse mit PHP mt_rand()

Statt über MySQL holen wir uns zufällige Daten mit dem Primärschlüssel id mit Hilfe von PHP und mt_rand(). Zuerst holen wir uns alle ids, der durch where eingeschränkten Datenmenge. Dann bestimmen wir aus diesen ids $NUM_OF_RESULTS zufällige ids und speichern diese im Array arr. Dabei prüfen wir, dass keine id doppelt vorkommt. Das kann bei kleinen Tabellen sehr inperformant sein, da die Schleife zur Ermittlung der zufälligen ids ggf. sehr häufig durchlaufen wird bis eindeutige ids ermittelt wurden. Zum Schluss suchen wir unsere zufälligen Tupel mittels den mit mt_rand() berechneten ids und dem MySQL Operator IN.

$query = $conn->query('SELECT id FROM table WHERE where');

$ids = array();
$arr = array();
$where = array();

$result = $query->fetchAll();
foreach ($result as $row) {
    $ids[] = $row[0];
}

$count = count($ids);
$i = 0;
// Ist nur performant, falls $NUM_OF_RESULTS viel kleiner ist als $count
while ($i < $NUM_OF_RESULTS) {
    $rand = mt_rand(0, $count - 1);
    if (!in_array($ids[$rand], $arr)) {
        $arr[] = $ids[$rand];
        $i++;
    }
}

$query = $conn->query('SELECT id FROM table WHERE id IN ('.implode(',', $arr).')');
$result = $query->fetchAll();
foreach ($result as $row) {
    echo $row[0].'<br />';
}

Vorteil: Daten sind wie bei ORDER BY RAND() zufällig ausgewählt. Die Methode ist schneller als ORDER BY RAND(). Allerdings ist dies stark von der Tabellengröße und der Größe der Ergebnismenge abhängig.

Nachteil: Schlecht bei Ergebnismengen, die fast so groß wie die Tabelle sind, und es geht noch schneller.

Mehode 2: LIMIT-Grenzen zufällig bestimmen

Eine weitere Möglichkeit ist es die LIMIT-Grenzen des Queries mit PHP zufällig zu bestimmen.

$query = $conn->query('SELECT COUNT(*) FROM table WHERE where');
foreach ($result as $row) {
    $rand = mt_rand(0, $row[0] - $NUM_OF_RESULTS);
}

$query = $conn->query('SELECT * FROM table WHERE where LIMIT '.$rand.','.$NUM_OF_RESULTS);
$result = $query->fetchAll();
foreach ($result as $row) {
    echo $row[0].'<br />';
}

Vorteil: Schneller als die oberen Methoden.

Nachteil: Es werden immer "zusammenhängende" Daten ausgewählt. Kein echter Zufall innerhalb der ausgewählten Daten.

Mehode 3: Zufällige Werte mittels Cronjob in die Tabelle schreiben

Wir fügen in die Tabelle eine neue Spalte rand ein. Diese Spalte berechnen wir in kurzen Abständen immer wieder mittels eines Cronjobs neu und belegen sie mit zufälligen Werten zwischen 0 und 1.

UPDATE table SET rand = RAND();

Um auf die Spalte schnell zugreifen zu können, versehen wir sie noch mit einem Index.

ALTER TABLE table ADD INDEX index_rand (rand ASC);

Nun sortieren wir die Daten nach der Spalte rand. Dies ist durch den Index eine relativ schnelle Operation und wir holen uns die ersten $NUM_OF_RESULTS Daten aus der Tabelle.

$query = $conn->query('SELECT * FROM table ORDER BY rand LIMIT 0,'.$NUM_OF_RESULTS);
$result = $query->fetchAll();
foreach ($result as $row) {
	echo $row[0].'<br />';
}

Vorteil: Die schnellste Methode.

Nachteil: Die Ergebnismenge ist bis zum nächsten Cronjob-Aufruf statisch.

Mehode 4: Zufällige Werte mittels Cronjob in die Tabelle schreiben kombiniert mit zufälligen LIMIT-Grenzen

Wir kombinieren Methode 2 und 3 zu Lasten der Performance um ein wenig zufälligere Ergebnisse als in Methode 2 bzw. 3 zu erhalten.

$query = $conn->query('SELECT COUNT(*) FROM table WHERE where');
foreach ($result as $row) {
    $rand = mt_rand(0, $row[0] - $NUM_OF_RESULTS);
}

$query = $conn->query('SELECT * FROM table WHERE where ORDER BY rand LIMIT '.$rand.','.$NUM_OF_RESULTS);
$result = $query->fetchAll();
foreach ($result as $row) {
    echo $row[0].'<br />';
}

Vorteil: Schneller als Methode 1 und zufälligere Ergebnisse als durch Methode 2 und 3.

Nachteil: In der Regel langsamer als Methode 2 und 3.