Read EXPLAIN QUERY PLAN

When a query feels slow, the temptation is to add an index and hope. SQLite can tell you exactly what it intends to do instead, and reading that plan takes seconds.

Prefix a query with EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
SELECT id, title FROM notes WHERE title = 'Plan Monday';

On a table with no index on title, the output looks like this:

QUERY PLAN
`--SCAN notes

SCAN means SQLite will read every row in notes and test each one against the WHERE clause. For a hundred rows that’s instant; for a million it’s your slow query.

After CREATE INDEX notes_title_idx ON notes(title);, the same command reports:

QUERY PLAN
`--SEARCH notes USING INDEX notes_title_idx (title=?)

SEARCH means SQLite jumps to the matching entries through an index instead of visiting every row. The parenthesis shows which index columns it can use. You may also see USING COVERING INDEX, which is better still: every column the query needs lives in the index, so SQLite never touches the table at all.

Reading multi-table plans

For a join, the plan prints one line per table, in the order SQLite will process them:

EXPLAIN QUERY PLAN
SELECT notes.title, tags.name
FROM notes
JOIN note_tags ON note_tags.note_id = notes.id
JOIN tags ON tags.id = note_tags.tag_id;

Each line is either a SCAN or a SEARCH. One SCAN on the outermost table is normal — something has to drive the loop. A SCAN on an inner table is the expensive pattern, because it repeats for every outer row.

Note that EXPLAIN QUERY PLAN doesn’t run the query. It only shows the strategy, so it’s safe to use on any statement, even an UPDATE or DELETE.

Plans depend on data

Look for a table scan or an index search. Measure with realistic data because a scan can be the right plan for a tiny table.

This is the mistake that catches people: they check the plan on a ten-row development database, see a SCAN, and add indexes that production never needed — or worse, they see a SEARCH in development and assume production behaves the same. SQLite’s planner uses what it knows about table sizes. Test the plan against a database that resembles the real one, and run ANALYZE; after bulk-loading data so the planner’s statistics match reality.