SQLite EXPLAIN QUERY PLAN: Reading the Output and Spotting Slow Queries

EXPLAIN QUERY PLAN Tells You How a Query Will Run

Before tuning a slow query, you need to know what SQLite is actually doing. EXPLAIN QUERY PLAN prints a short summary of the strategy the planner picked — which tables it touches, in what order, and which indexes (if any) it uses. The query itself doesn't run; you just get the plan.

Drop the keywords in front of any statement:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

The output looks something like:

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

That single line tells you a lot: SQLite is doing a SEARCH (not a scan), on the users table, using the auto-created unique index for email, with email as the lookup key. Exactly what you'd hope for.

SCAN vs SEARCH: The First Thing to Read

Every line in the plan starts with either SCAN or SEARCH. The distinction is the most important signal in the whole output.

• SCAN <table> — SQLite reads every row of the table (or every entry of an index). Cost grows with table size.
• SEARCH <table> USING ... — SQLite jumps directly to matching rows via an index or primary key. Cost grows with the size of the result, not the table.

Here's a side-by-side. One column has an index, the other doesn't:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

The first plan reports SEARCH orders USING INDEX idx_orders_customer. The second reports SCAN orders — no index on status, so SQLite reads every row. On a small table that's invisible; on a million-row table it's the difference between milliseconds and seconds.

A SCAN isn't always wrong. For tiny lookup tables, or queries that genuinely return most of the rows, scanning is the right plan. But on a large table with a selective filter, SCAN is your cue to add an index.

Confirming an Index Is Being Used

The phrase to look for is USING INDEX <name> (or USING COVERING INDEX <name> — more on that below). If you created an index hoping the planner would pick it up, this is how you check:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

You should see SEARCH events USING INDEX idx_events_user (user_id=?). If instead the plan says SCAN events, something's blocking the planner from using the index — common causes are wrapping the column in a function (WHERE lower(user_id) = ...), comparing different types, or using LIKE '%foo%' with a leading wildcard.

A quick test of that:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    kind TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

That + 0 defeats the index — the plan falls back to SCAN events. Any expression on the indexed column does the same.

Covering Indexes Show Up Differently

When an index contains every column the query needs, SQLite can answer the query from the index alone, without touching the table. The plan reports USING COVERING INDEX:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

The plan: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). The query asks for price, the index already stores sku and price, so SQLite never reads the underlying table. Covering indexes are the fastest plan you can get for a lookup — worth knowing about when you're picking which columns to index together.

Reading Join Plans

Joins are where plans get interesting. Each line of the plan corresponds to one table in the join, and the order of the lines is the order SQLite visits them. The first table is the outer table; later tables are looked up once per outer row.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

A typical plan:

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

Read it top down: SQLite finds the one customer by primary key, then for that customer looks up matching orders via the index on customer_id. Both lines are SEARCH — no full scans — which is what you want.

If you saw SCAN o on the second line instead, every customer lookup would trigger a full pass over orders. On a large table that's catastrophic. The fix is almost always an index on the join column.

Compound Queries and Subqueries

Plans for UNION, EXCEPT, and subqueries nest. Each branch shows up indented under its parent:

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

You'll see two child rows under a COMPOUND QUERY heading, one per branch. Subqueries and CTEs work similarly — each gets its own indented plan node, and you read each one with the same SCAN vs SEARCH lens.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

The subquery becomes a separate plan node ("LIST SUBQUERY" or similar), with its own access strategy. Apply the same checks at every level.

EXPLAIN vs EXPLAIN QUERY PLAN

These are two different things, and people mix them up.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (no QUERY PLAN) dumps the bytecode SQLite's virtual machine will execute — dozens of low-level opcodes like OpenRead, SeekRowid, Column, ResultRow. Useful if you're debugging the engine itself. Almost never useful for tuning.

EXPLAIN QUERY PLAN is the human-readable summary you actually want. When in doubt, always reach for EXPLAIN QUERY PLAN.

A Workflow for Slow Queries

When a query is slow, the loop looks like this:

1. Run EXPLAIN QUERY PLAN on it.
2. For each table line, ask: is this SCAN or SEARCH? On a big table, SCAN is the suspect.
3. If a SCAN is filtering on some column, consider an index on that column.
4. For joins, confirm the inner-loop tables use SEARCH USING INDEX on the join column.
5. Re-run EXPLAIN QUERY PLAN after adding the index. The plan should change. If it didn't, the planner decided your index wasn't worth using — usually because the table is small or the filter isn't selective enough.

A worked example of step 5:

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SCAN logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SEARCH logs USING INDEX idx_logs_level (level=?)

The plan changed from SCAN to SEARCH. That's the signal the index is doing its job. (On a fresh, near-empty table the planner may still scan because there isn't enough data to bother with the index — populate the table or run ANALYZE and the choice often flips.)

What the Plan Won't Tell You

EXPLAIN QUERY PLAN describes strategy, not cost. It won't tell you the query took 800 ms or returned 50,000 rows. For that you want timing (.timer on in the CLI) and row counts. The plan and the timing are complementary — the plan tells you why a query is slow, the timer tells you whether it is.

Two more limitations worth knowing:

• The plan can change as data grows. A query that scanned a 100-row table happily will need an index when the table hits a million rows. Re-check plans on production-sized data, not your dev fixtures.
• The planner uses statistics gathered by ANALYZE. Without them, it falls back to defaults that aren't always great. Stale or missing stats are a common reason for surprising plans.

Next: ANALYZE and VACUUM

The query planner makes its decisions based on statistics about your tables and indexes. If those stats are missing or outdated, even a perfectly-indexed schema can produce a bad plan. ANALYZE is how you keep them fresh — and VACUUM is the companion command for reclaiming space and defragmenting the database file. That's next.