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
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';
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.