von Markus Winand.

SQLite Skripts für den “3-Minuten Test”


Dieser Abschnitt enthält die create, insert und select Kommandos für den „3-Minuten Test“. Mach den Test doch selbst, bevor du weiter liest.

Die create und insert Anweisungen findest du in diesem Download.

Frage 1 — DATE Anit-Pattern

CREATE INDEX tbl_idx ON tbl (date_column)
SELECT COUNT(*)
  FROM tbl
 WHERE STRFTIME ('%Y', date_column) = '2017'
SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= '2017-01-01'
   AND date_column <  '2018-01-01'

Die erste Abfrage liest die ganze Tabelle (SCAN TABLE). Die zweite Abfrage kann den Index nutzen (SEARCH TABLE):

0|0|0|SCAN TABLE tbl USING COVERING INDEX tbl_idx
0|0|0|SEARCH TABLE tbl USING COVERING INDEX tbl_idx 
                  (date_column>? AND date_column<?)

Frage 2 — Indizierte Top-N-Abfrage

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 LIMIT 1

Die Abfrage kann den Index nutzen und muss keine Sortierung durchführen:

0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (a=?)

Frage 3 — Spaltenreihenfolge

CREATE INDEX tbl_idx ON tbl (a, b)
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1
SELECT *
  FROM tbl
 WHERE b = 1
DROP INDEX tbl_idx 
CREATE INDEX tbl_idx ON tbl (b, a)
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1
SELECT *
  FROM tbl
 WHERE b = 1

Die erste Abfrage kann beide Indizes optimal nutzen:

0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (a=? AND b=?)
0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (b=? AND a=?)

Die zweite Abfrage kann den ersten Index jedoch nicht nutzen und liest stattdessen die ganze Tabelle:

0|0|0|SCAN TABLE tbl

Dreht man die Spaltenreihenfolge im Index um, kann die zweite Abfrage den Index auch nutzen:

0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (b=?)

Frage 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text)
PRAGMA case_sensitive_like = true
SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%'

Die Abfrage kann den Index optimal nutzen:

0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (text>? AND text<?)

Beachte, dass dafür das obige pragma benötigt wird. Ohne dieser Einstellung ignoriert like bei SQLite die Groß-/Kleinschreibung und kann diesen Index nicht nutzen.

Frage 5 — Index-Only-Scan

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

Beide Indizes können den Index optimal nutzen, um die gesuchten Zeilen zu finden. Der erste Index enthält darüber hinaus alle Spalten, die von der Abfrage benötigt werden, sodass kein Tabellenzugriff nötig ist (COVERING). Da die zweite Abfrage zusätzlich auf die Tabelle zugreifen muss, ist sie langsamer.

0|0|0|SEARCH TABLE tbl USING COVERING INDEX tbl_idx (a=?)
0|0|0|SEARCH TABLE tbl USING INDEX tbl_idx (a=?)

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Sein Buch bei Amazon kaufen

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz