Reading SQLite's EXPLAIN QUERY PLAN

2026-04-19 · Alex Moreno

SQLite will tell you exactly how it intends to run a query if you ask nicely:

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at;

The output I got on a fresh table was:

SCAN orders
USE TEMP B-TREE FOR ORDER BY

SCAN means a full table scan, and the temporary B-tree means sorting happens after the fact. Both are fine for a thousand rows and painful for a million.

Adding the right index

CREATE INDEX idx_orders_customer_created
  ON orders(customer_id, created_at);

Now the plan reads:

SEARCH orders USING INDEX idx_orders_customer_created (customer_id=?)

No scan, no temporary sort. The column order in the index matches the filter first and the sort second, which is the whole trick.

← All posts