Indizes

Wie findet die Datenbank die Bestellungen von Kunde 3? Ohne Hilfe liest sie die Tabelle bestellungen Zeile für Zeile, alle, und behält die, deren kunde_id 3 ist. Bei 30 Zeilen geht das sofort; bei 30 Millionen dauert es eine Weile.

Ein Index funktioniert wie das Register am Ende eines Buchs: Statt das ganze Buch zu lesen, schlägst du ein Wort nach und gehst direkt zu seinen Seiten. Ein Index auf einer Spalte hält die Werte der Spalte sortiert, jeden mit den Zeilen, in denen er steht, und in einer sortierten Liste findet die Datenbank einen Wert schnell. So erstellst du einen:

CREATE INDEX bestellungen_kunde_id ON bestellungen (kunde_id);

Ein Index hat einen Namen (meist die Tabelle und die Spalte), und nach ON kommen die Tabelle und, in Klammern, die Spalte. Du erwähnst den Index nie in deinen Abfragen: Die Datenbank entscheidet selbst, wann er hilft. Die Abfragen bleiben gleich und finden dieselben Zeilen, nur schneller.

Um zu sehen, was die Datenbank vorhat, setze EXPLAIN QUERY PLAN vor eine Abfrage. Ihr Ergebnis hat mehrere Spalten; wichtig ist detail. Ohne den Index:

EXPLAIN QUERY PLAN
SELECT * FROM bestellungen WHERE kunde_id = 3;

SCAN bedeutet, dass die Datenbank die ganze Tabelle liest. Mit dem Index sucht sie:

SEARCH bedeutet, dass sie den Wert im Index nachschlägt, hier in bestellungen_kunde_id. Die Klammern sagen, nach welcher Spalte sie sucht, und das Fragezeichen steht für den Wert, den sie sucht, hier 3.

Die genauen Worte hängen von der Version von SQLite ab; wichtig ist SCAN oder SEARCH. Ein Index hilft bei großen Tabellen, bei den Spalten, nach denen Abfragen in WHERE suchen, in JOIN … ON verbinden und in ORDER BY sortieren. Ein Primärschlüssel hat einen eigenen Index, ebenso jede UNIQUE-Spalte (so prüft die Datenbank schnell, dass ein Wert noch nicht da ist). Fremdschlüssel bekommen in SQLite keinen, und da Tabellen ständig über sie verbunden werden, sind sie oft die ersten Spalten, die einen bekommen. Ein Index kann auch mehrere Spalten haben: ON spiele (genre, preis) hilft Abfragen, die nach dem Genre oder nach Genre und Preis suchen, wie ein Telefonbuch, das nach Nachname und Vorname sortiert ist.

Ein Index ist aber nicht umsonst. Er braucht Platz, und jedes INSERT, UPDATE und DELETE muss alle Indizes der Tabelle aktualisieren, was sie langsamer macht. Deshalb bekommen Tabellen nicht für jede Spalte einen Index, sondern nur für die, die Abfragen wirklich brauchen. Und bei kleinen Tabellen wie denen von Pixel Shop macht ein Index keinen Unterschied, den du bemerken würdest.

CREATE UNIQUE INDEX erstellt einen Index, der außerdem jeden Wert nur einmal erlaubt, genau wie UNIQUE. So fügst du diese Regel einer Tabelle hinzu, die es schon gibt, was das ALTER TABLE von SQLite nicht kann. Einen Index, den du nicht brauchst, löschst du mit DROP INDEX und seinem Namen.

Typischer Fehler: die Spalte in eine Funktion zu packen. Ein Index auf email hilft bei WHERE LOWER(email) = '…' nicht: Der Index enthält die E-Mail-Adressen, wie sie sind, nicht in Kleinbuchstaben, also muss die Datenbank LOWER() trotzdem für jede Zeile berechnen.