MySQL – To JOIN or not to JOIN

Als PHP Entwickler ist man ständig damit beschäftigt etwas aus einer Datenbank zu laden. Dabei kommen auch die ominösen Joins zum Einsatz, von dem alle immer erzählen sie wären böse. In diesem Artikel will ich mich genau mit diesem Vorurteil beschäftigen.

Problemstellung

Es geht bei der Diskussion um Joins generell um die Frage der Performance. Für die Praxis relevant wird es also erst bei größeren Datenmengen (Tabellen mit mehr als 1.000 Zeilen) oder sehr komplizierten Abfragen. Dazu kommt natürlich, dass sich die gewünschten Daten über mehrere Tabellen verteilen.

Für den weiteren Verlauf nehmen wir folgendes Beispiel:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(70) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `log` (
  `id` int(11) NOT NULL,`userId` int(11) DEFAULT NULL,`created` datetime DEFAULT NULL,`data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `users` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(70) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

CREATE TABLE `log` (

`id` int(11) NOT NULL,`userId` int(11) DEFAULT NULL,`created` datetime DEFAULT NULL,`data` text,

PRIMARY KEY (`id`)

) ENGINE=InnoDB;

Wir wollen eine Liste aller Logeinträge ab einem bestimmten Datum und möchten dazu die Namen der Benutzer anzeigen.

Lösungsmöglichkeiten

Abruf der Daten wenn sie benötigt werden

$pdo = new PDO('');

$stmt = $pdo->query('SELECT * FROM log WHERE created > "'.date('Y-m-d', strtotime('1 week ago')).'"');

while ($log = $stmt->fetch(PDO::FETCH_OBJ)) {
   $username = $pdo->query('SELECT name FROM users WHERE userId = '.$log->userId)->fetchColumn();
   
   echo '<tr><td>'.$log->created.'</td><td>'.$log->data.'</td><td>'.$username.'</td></tr>';
}

$pdo = new PDO('');

$stmt = $pdo->query('SELECT * FROM log WHERE created > "'.date('Y-m-d', strtotime('1 week ago')).'"');

while ($log = $stmt->fetch(PDO::FETCH_OBJ)) {

$username = $pdo->query('SELECT name FROM users WHERE userId = '.$log->userId)->fetchColumn();

echo '<tr><td>'.$log->created.'</td><td>'.$log->data.'</td><td>'.$username.'</td></tr>';

}

Die vermutlich schlechteste Methode das Problem zu lösen. Hier wird pro Logeintrag eine Datenbankabfrage nach dem Benutzernamen abgefeuert. Dass das nicht gut für die Performance sein kann, ist jedem klar und so wie oben wird das auch niemand praktizieren – hoffe ich zumindest.
Allerdings kann man sehr leicht in diese Falle tappen, wenn die Datenbank hinter einem ORM abstrahiert ist.

$logs = Log::findAll('created > "'.date('Y-m-d', strtotime('1 week ago')).'"');
foreach ($logs as $log) {
   echo '<tr><td>'.$log->created.'</td><td>'.$log->data.'</td><td>'.$log->getUser()->name.'</td></tr>';
}

$logs = Log::findAll('created > "'.date('Y-m-d', strtotime('1 week ago')).'"');

foreach ($logs as $log) {

echo '<tr><td>'.$log->created.'</td><td>'.$log->data.'</td><td>'.$log->getUser()->name.'</td></tr>';

}

Ups! Sieht zwar unverdächtig aus, ist aber genau das selbe was oben steht, nur durch die Abstraktion verschleiert. Die meisten ORMs werden zwar den einzelnen Benutzer intern cachen und somit bei sich wiederholenden Benutzern etwas weniger Abfragen abschicken, aber viel besser wird es damit auch nicht.

Ein Request mit einem JOIN

Die selbe Sache sieht mit Join dann so aus:

$pdo = new PDO('');

$stmt = $pdo->query('SELECT l.*, u.name FROM log JOIN users ON l.userId = u.id 
    WHERE l.created > "'.date('Y-m-d', strtotime('1 week ago')).'"');

while ($log = $stmt->fetch(PDO::FETCH_OBJ)) {
   echo '<tr><td>'.$log->created.'</td><td>'.$log->data.'</td><td>'.$log->name.'</td></tr>';
}

$pdo = new PDO('');

$stmt = $pdo->query('SELECT l.*, u.name FROM log JOIN users ON l.userId = u.id

WHERE l.created > "'.date('Y-m-d', strtotime('1 week ago')).'"');

while ($log = $stmt->fetch(PDO::FETCH_OBJ)) {

echo '<tr><td>'.$log->created.'</td><td>'.$log->data.'</td><td>'.$log->name.'</td></tr>';

}

Die Verknüpfung der Einträge übernimmt jetzt die Datenbank für uns. Im Vergleich zu oben haben wir hier einen massiven Performance-Gewinn. Wichtig bei Joins ist aber immer, sie mit einen EXPLAIN zu überprüfen, nicht dass die Join-Bedingungen ohne Index arbeiten.

Prepared Statement und Stored Procedure

Einige SQL-Dialekte lassen noch weitere Möglichkeiten zu unser Problem zu lösen. So könnten wir auch mit einem Prepared Statement den Benutzernamen laden.

$pdo = new PDO('');

$stmt     = $pdo->query('SELECT * FROM log 
    WHERE created > "'.date('Y-m-d', strtotime('1 week ago')).'"');
$userStmt = $pdo->prepare('SELECT name FROM users WHERE userId = :uid');

while ($log = $stmt->fetch(PDO::FETCH_OBJ)) {
   $userStmt->execute([':uid' => $log->userId]);
   $username = $userStmt->fetchColumn();

   echo '<tr><td>'.$log->created.'</td><td>'.$log->data.'</td><td>'.$username.'</td></tr>';
}

$pdo = new PDO('');

$stmt = $pdo->query('SELECT * FROM log

WHERE created > "'.date('Y-m-d', strtotime('1 week ago')).'"');

$userStmt = $pdo->prepare('SELECT name FROM users WHERE userId = :uid');

while ($log = $stmt->fetch(PDO::FETCH_OBJ)) {

$userStmt->execute([':uid' => $log->userId]);

$username = $userStmt->fetchColumn();

echo '<tr><td>'.$log->created.'</td><td>'.$log->data.'</td><td>'.$username.'</td></tr>';

}

Bevor die Datenbank überhaupt nach Einträgen sucht, muss unser Query erst compiliert und optimiert werden. Das ist für so ein einfaches Query eine Menge Überhang, der sich durch ein Prepared Statement sparen lässt. Wir sind hier mit der Performance also zwischen den ersten beiden Varianten. Da entscheidet dann wie üblich die Praxis. 100 Einträge aus der Logtabelle -> Alles OK. 100.000 Einträge aus der Logtabelle -> Problem.

Mit einer Stored Procedure verhält es sich genauso. Vom Code her gibt es nicht viel zu sehen, aber der Vollständigkeit halber hier:

$pdo = new PDO('');

$stmt = $pdo->query('SELECT * FROM log WHERE created > "'.date('Y-m-d', strtotime('1 week ago')).'"');

while ($log = $stmt->fetch(PDO::FETCH_OBJ)) {
   $username = $pdo->query('call get_user_name('.$log->userId.')')->fetchColumn();

   echo '<tr><td>'.$log->created.'</td><td>'.$log->data.'</td><td>'.$username.'</td></tr>';
}

$pdo = new PDO('');

$stmt = $pdo->query('SELECT * FROM log WHERE created > "'.date('Y-m-d', strtotime('1 week ago')).'"');

while ($log = $stmt->fetch(PDO::FETCH_OBJ)) {

$username = $pdo->query('call get_user_name('.$log->userId.')')->fetchColumn();

echo '<tr><td>'.$log->created.'</td><td>'.$log->data.'</td><td>'.$username.'</td></tr>';

}

Fazit

„Joins sind böse“ ist bei weitem zu pauschal. Joins sind durchaus die beste Lösung für bestimmte Problemstellungen. Die Aussage sollte also vielmehr als Denkanstoß betrachtet werden. Auch zur Frage: Warum sind die Daten überhaupt auf mehrere Tabellen verteilt? Würden wir in unserem Beispiel den Benutzernamen ins Log übernehmen, wäre das Problem auch gelöst – aber die Datenbank nicht mehr in Normalform.