Constraints and Indexes

Index Plan

How a Search Is Found

An index helps the database find matching rows. SQLite can explain the search plan.

Program

Play the script to create an index and inspect the plan.

index_plan.sql
Replay: real traced execution (multi-file project)
CREATE TABLE logs (id INTEGER, level TEXT, message TEXT);
INSERT INTO logs VALUES (1, 'info', 'start'), (2, 'error', 'fail'), (3, 'info', 'done');
CREATE INDEX idx_logs_level ON logs(level);
EXPLAIN QUERY PLAN SELECT message FROM logs WHERE level = 'error';

  1. logs ← 0 rows

    1CREATE TABLE logs (id INTEGER, level TEXT, message TEXT);2INSERT INTO logs VALUES (1, 'info', 'start'), (2, 'error', 'fail'), (3, 'info', 'done');
    values this step0 rowslogs

  2. logs ← 3 rows

    1CREATE TABLE logs (id INTEGER, level TEXT, message TEXT);2INSERT INTO logs VALUES (1, 'info', 'start'), (2, 'error', 'fail'), (3, 'info', 'done');3CREATE INDEX idx_logs_level ON logs(level);
    values this step3 rowslogs

  3. indexes ← 1 row

    2INSERT INTO logs VALUES (1, 'info', 'start'), (2, 'error', 'fail'), (3, 'info', 'done');3CREATE INDEX idx_logs_level ON logs(level);4EXPLAIN QUERY PLAN SELECT message FROM logs WHERE level = 'error';
    values this step1 rowindexes

  4. result ← 1 row

    3CREATE INDEX idx_logs_level ON logs(level);4EXPLAIN QUERY PLAN SELECT message FROM logs WHERE level = 'error';
    values this step1 rowresult

Follow the Plan

  1. logs starts with three rows: info/start, error/fail, and info/done.
  2. CREATE INDEX idx_logs_level ON logs(level) builds an index on level.
  3. The query asks for messages where level = 'error'.
  4. SQLite explains that it will search with idx_logs_level.
  5. The plan detail is SEARCH logs USING INDEX idx_logs_level (level=?). | part | value | | --- | --- | | matching level | error | | indexed column | level | | index name | idx_logs_level | | plan detail | SEARCH logs USING INDEX idx_logs_level (level=?) |
CREATE INDEX `CREATE INDEX` builds a lookup structure.
query plan `EXPLAIN QUERY PLAN` shows how SQLite intends to search.
indexed search The plan shows the index name when the lookup can use it.

Exercise: index_plan.sql

Reproduce the plan detail, then identify the index name and column used by the search.