Reading SQLite query plans

Run a small experiment

Install the SQLite command-line shell if you do not already have it. Download experiment.sql, then run these shell commands from the download directory. Choose a new filename so you do not modify an existing database.

sqlite3 --version
sqlite3 basics.db < experiment.sql
sqlite3 basics.db

The script creates 10,000 orders, shows plans before and after indexes, and leaves the test data available. In the SQLite prompt, try:

EXPLAIN QUERY PLAN
SELECT total FROM orders WHERE customer_id = 42;

EXPLAIN QUERY PLAN describes the strategy without running the SELECT to completion. Running the SELECT without that prefix retrieves the rows. Plain EXPLAIN lists the instructions SQLite executes in its virtual machine. This series uses EXPLAIN QUERY PLAN instead.

Type .quit to leave the shell. Deleting your disposable database file afterward removes the experiment. Commands beginning with a dot are shell commands, not SQL for your application.

Read the access path

SEARCH orders USING INDEX orders_customer_idx (customer_id=?)

SEARCH orders

SQLite uses a lookup or range to visit a subset of entries. That subset may still be large.

USING INDEX

The named index supplies the lookup. SQLite may still fetch table rows for values missing from that index.

(customer_id=?)

The customer_id equality narrows the search. The question mark stands for the lookup value. It can appear even when the SQL contains a literal value such as 42.

SCAN orders

A scan walks through a table or index rather than looking up a bounded subset. A LIMIT can still stop it early.

COVERING INDEX

The index supplies all the values this access needs, so separate table-row lookups are unnecessary.

EXPLAIN QUERY PLAN shows the chosen strategy, not measured performance. It does not print actual or estimated row counts, per-node elapsed times, or loop counts. Measure those separately with counts, timings, or optional SQLite counters.

A lookup can still inspect rejected candidates

SELECT total FROM orders
WHERE customer_id = 42 AND created_at >= '2025-01-01';

-- With an index on customer_id alone:
-- SEARCH orders USING INDEX orders_customer_idx (customer_id=?)

The displayed search bound mentions customer_id, not the date. SQLite must still check the date condition. The animation uses invented counters to show which candidates pass the date filter. SQLite does not print these counters in the plan.

Teaching counters (not SQLite plan output)
Candidates checked: 0
Returned: 0
Rejected: 0

Why reading the table can be reasonable

If few rows match, an index can avoid reading unrelated table rows. If almost all rows match and the index is not covering, following many index entries and then fetching their table rows may cost more than reading the table directly.

Judge the work rather than the SCAN or SEARCH label. How many rows does the query need? Does the chosen path supply the requested order and values?

Read the loop order

SELECT c.id, o.id
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.id <= 5;

-- One possible plan:
|--SEARCH c USING INTEGER PRIMARY KEY (rowid<?)
`--SEARCH o USING COVERING INDEX orders_customer_idx (customer_id=?)

SQLite executes joins using nested loops. Here, the outer loop finds customers. For each customer, the inner loop finds matching orders. An INTEGER PRIMARY KEY in an ordinary rowid table is an alias for the rowid, which explains the rowid wording.

Five customers with two matching orders each produce ten pairs. These counts describe the example data, not plan output. SQLite can choose a different loop order from the table order in the SQL. Check the plan.

Teaching counters (not SQLite plan output)
Rows per lookup: —
Lookups completed: 0
Total rows so far: 0

Sorting can prevent an early stop

EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
ORDER BY created_at DESC LIMIT 20;

-- Without a suitable ordering index:
|--SCAN orders
`--USE TEMP B-TREE FOR ORDER BY

The scan supplies candidate rows and a temporary sorting structure supplies the requested order. Without ordered access, SQLite must consider all qualifying candidates to know which 20 are newest. Returning only 20 rows does not imply reading only 20.

A date index can provide the order directly. In the downloadable experiment, the later plan is SCAN orders USING COVERING INDEX orders_date_idx, with no separate sorting entry. Scanning backward can produce descending order even when the index was created with its default ascending order.

Connect a plan to a prediction

Exercises

SCAN orders USING COVERING INDEX orders_date_idx

SEARCH orders USING INDEX orders_customer_idx (customer_id=?)

SELECT o.id, o.created_at
FROM customers c JOIN orders o ON o.customer_id=c.id
WHERE c.segment='trial'
ORDER BY o.created_at DESC LIMIT 20;

-- Example EXPLAIN QUERY PLAN detail:
|--SCAN c
|--SEARCH o USING INDEX orders_customer_idx (customer_id=?)
`--USE TEMP B-TREE FOR ORDER BY

Reading checklist

1. State what the query must return, including ordering and limits.
2. Identify each SCAN or SEARCH and the constraints shown beside it.
3. For joins, follow the nesting order of the table accesses.
4. Look for covering access and temporary sorting structures.
5. Separate what the plan shows from counts or timings that still need measuring.

Sources and next step

Lesson 2 explains the statistics behind these choices, including what you can inspect when the plan does not print estimates.

• EXPLAIN QUERY PLAN: access paths, nesting order, and sorting entries.
• SQLite command-line shell: opening files and measuring queries.
• Query planning: table lookups, indexes, and ordering.