Indexes, ANALYZE and EXPLAIN QUERY PLAN

How the planner chooses, covering and partial and expression indexes, reading a query plan, and keeping statistics fresh.

Reading the plan

explain query plan
select b.title from book b join author a on a.id = b.author_id where a.name = 'Le Guin';

-- SEARCH a USING INDEX author_name_idx (name=?)
-- SEARCH b USING INDEX book_author_idx (author_id=?)

explain query plan
select * from book where title like '%dispossessed%';
-- SCAN book          <- no index can help a leading wildcard

explain query plan
select count(*) from book where status = 'published';
-- SCAN book USING COVERING INDEX book_status_idx
-- COVERING INDEX means every needed column is in the index: no table lookup

• Run the plan with explain query plan: it needs no data and is fast enough to use during development.
• pragma vdbe_opcode_trace and explain show the full bytecode, which is useful only for genuinely obscure cases.
• The rightmost table in the plan is scanned first in SQLite's output ordering; read it as the access order, inner to outer.

Index shapes

-- composite: order matters, leftmost prefix rule applies
create index book_author_published on book (author_id, published desc);

-- covering: includes the columns a hot query reads
create index book_author_cover on book (author_id, published desc, title);

-- partial: index only the pending rows in a mostly-processed queue
create index job_pending on job (run_after)
  where status = 'pending';

-- expression: index a computed value
create index book_slug on book (lower(replace(title, ' ', '-')));
create index book_year on book (cast(strftime('%Y', published) as integer));

-- unique index instead of a unique constraint, when you want a partial unique
create unique index one_active_sub on subscription (account_id)
  where ended_at is null;

-- see what exists
select name, tbl_name, sql from sqlite_master where type = 'index' and tbl_name = 'book';

• An expression index is only used if the query contains the identical expression. lower(title) matches lower(title); title collate nocase does not.
• A partial index is only used when the query's WHERE clause implies the index's WHERE clause. where status = 'pending' matches; where status != 'done' does not.
• Covering indexes trade disk and write cost for read speed. Check with EXPLAIN QUERY PLAN that the planner actually uses the covering form.
• Every index must be updated on insert, update and delete. Ten indexes on a write-heavy table can triple the write cost.

-- statistics: run after a large change, not in a hot write path
analyze;
analyze book;          -- or a single table, which is cheaper

-- with statistics, the planner can prefer the better of two indexes
-- without them, add an explicit hint by rewriting the query to be more selective

Pragmas that affect query speed

pragma cache_size = -64000;      -- negative means KiB: 64 MB page cache
pragma temp_store = memory;      -- temp tables and sorts in RAM
pragma mmap_size = 268435456;    -- 256 MB of memory-mapped I/O
pragma page_size;                -- must be set before the first table is created
pragma cache_spill;

-- prefer an index for a small table when the planner guesses wrong
analyze sqlite_schema;           -- or sqlite_master on older versions
-- and consider: ANALYZE sqlite_schema; update sqlite_stat1 set stat = '...'

• cache_size is the single most effective read tunable: it keeps hot pages in memory instead of reading them from the file.
• mmap_size helps on 64-bit systems with a database that fits in address space, and the value must be set per connection.
• temp_store = memory moves temporary B-trees for sorts and grouping into memory; watch memory use on a large sort.
• page_size only takes effect when the database is created - changing it later requires VACUUM.
• Do not tune by guessing. Measure with a realistic dataset and the same statements the application issues.

FAQ

Related

Querying with SQL: joins, grouping, upserts and CTEs Testing, tooling and benchmarks for SQLite apps

Last refreshed 2026-09-18.