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 Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);

SELECT COUNT(*)
  FROM tbl
 WHERE STRFTIME ('%Y', date_column) = '2024';

SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= '2024-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=?)