SQLite performance can hinge on a single missing index. In a recent post, Simon Willison highlights a “SQLite Query Explainer” that helps translate EXPLAIN QUERY PLAN output into human-friendly insights.
Here’s a quick, practical guide to reading that output and turning it into fast wins for your app or data workflow.
What to look for in EXPLAIN QUERY PLAN
- SCAN vs SEARCH: “SCAN” means a full table scan; “SEARCH” means an index is being used. Prefer SEARCH on large tables.
- USING INDEX / USING COVERING INDEX: A covering index satisfies the query without extra table lookups—often the biggest speedup.
- USING TEMP B-TREE: SQLite is sorting or de-duplicating in memory. An index on the ORDER BY or DISTINCT columns can avoid this.
- Join keys: Ensure indexes exist on columns used in JOINs and WHERE filters, especially for the child table in nested loops.
- Filters and range lookups: Composite index order matters—put the most selective equality filters first, then range columns.
Deeper reference: the official EXPLAIN QUERY PLAN docs and query planner overview from SQLite.
The 3‑minute workflow
- Run: EXPLAIN QUERY PLAN <your SELECT…> in your SQLite shell or client.
- Scan the output for SCAN, USING TEMP B-TREE, and missing indexes on join/filter columns.
- Add or adjust indexes (often composite) to match WHERE, JOIN, and ORDER BY.
- Rerun EXPLAIN QUERY PLAN. Aim for SEARCH with USING INDEX/COVERING INDEX and no TEMP B-TREE.
- For complex queries, break them into parts and verify each piece’s plan.
Fast indexing wins
- WHERE a = ? AND b = ? AND c BETWEEN ? AND ? → index on (a, b, c).
- JOIN t2 ON t1.k = t2.k → index on t2(k) if t2 is the inner/child of the join.
- ORDER BY created_at DESC LIMIT 50 → index on (created_at DESC) to avoid a temp sort.
- Covering queries: include selected columns in the index to turn “USING INDEX” into “USING COVERING INDEX.”
Gotchas to remember
- Small tables: a SCAN can be fine—don’t over-index.
- Keep stats fresh: run ANALYZE so the planner has up-to-date information.
- Parameterization: the plan may differ with different literals—test realistic cases.
- Trade-offs: indexes speed reads but slow writes and take space—measure before and after.
Takeaway: read EXPLAIN QUERY PLAN, target SCANs and TEMP B-TREE with the right indexes, and iterate until you see SEARCH with (covering) indexes. Simple, repeatable, fast.
Enjoy this? Get one practical AI and developer nugget in your inbox each week. Subscribe to The AI Nuggets →
