Your Query Is Slow. EXPLAIN Told You Why: Five Plan Checks That Catch It Before Users Do

A slow query is a claim. The plan is the evidence.

When a query is slow, the usual response is to rewrite it and hope. The plan gives you something better than hope: a one-line-per-step account of what the database will actually do. In SQLite, the command is EXPLAIN QUERY PLAN followed by your query, no changes to the query itself. Two words do most of the work. SCAN means the engine walks every row of a table. SEARCH means it uses an index to jump to the rows it needs. A plan that says SCAN on a table you expect to be searched is the whole diagnosis, in one word.

I ran five checks on a synthetic dataset built for this article: 5,000 customers and 100,000 orders, generated with seed 42 in SQLite 3.45.1. Timings are medians of three runs on an in-memory database, so treat them as relative, not as production numbers. What transfers to your database is the plan reading, not the milliseconds.

Setup, so you can reproduce every number:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, region TEXT);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount REAL,
  order_date TEXT,
  status TEXT
);

Check 1: the point lookup that scanned 100,000 rows

The query asks for one customer's orders. There are 15 of them.

SELECT * FROM orders WHERE customer_id = 4321;

Before any index:

SCAN orders

Median time: 5.09ms. After one index:

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)

Median time: 0.02ms. That is a 250-fold speedup from a single line, and the plan told you the fix before you applied it. If your lookup query shows SCAN, the missing index is usually the first thing to check, not the last.

Check 2: the range count that became a covering index

SELECT COUNT(*) FROM orders WHERE amount > 450;

Before the index, the plan is SCAN orders, median 5.57ms, and the count is 9,949 rows. After creating an index on amount, the plan changes to something worth noticing:

SEARCH orders USING COVERING INDEX idx_orders_amount (amount>?)

Median time: 0.14ms. "Covering" means the index alone holds everything the query needs, the count and the filter column, so the engine never touches the table rows at all. When a query only needs indexed columns, a covering index is the cheapest shape it can take. If your plan says SEARCH but not COVERING, and the query is still slow, check whether it is fetching table rows for columns the index does not hold.

Check 3: read a join plan from the small side

SELECT COUNT(*)
FROM orders o
JOIN customers c ON c.region = 'North' AND o.customer_id = c.id;

The plan:

SCAN c | SEARCH o USING COVERING INDEX idx_orders_customer (customer_id=?)

Read it left to right. The engine scans the small side, customers filtered to the North region, then searches the large side, orders, by index for each matching customer. The count is 24,479, median time 1.86ms. This is the shape you want in a join plan: SCAN on the small table, SEARCH on the big one. If the plan shows SCAN on both sides of a join, the big table has no usable index on the join key, and that is your fix. Note the trap in the other direction: a SCAN on a tiny dimension table is fine. Panic about SCAN only when the scanned table is large.

Check 4: the sort that built a temp table

SELECT * FROM orders ORDER BY status LIMIT 20;

The plan:

SCAN orders | USE TEMP B-TREE FOR ORDER BY

Median time: 10.34ms, the slowest query in this article, and it only returns 20 rows. The engine had to sort all 100,000 rows to find the first 20, because status has no index to walk in order. Compare the same shape on an indexed column:

SELECT * FROM orders ORDER BY amount LIMIT 20;

SCAN orders USING INDEX idx_orders_amount

Median time: 0.03ms. No temp sort, because the index already stores rows in amount order, so the engine walks it and stops after 20. The lesson is specific: LIMIT does not save a query that sorts first. An index on the sort column does, when the sort matches the index order.

Check 5: the index that did not rescue LIKE

This is the check that keeps the other four honest. On the 5,000-row customers table:

SELECT COUNT(*) FROM customers WHERE name LIKE 'Customer 42%';
SELECT COUNT(*) FROM customers WHERE name LIKE '%42%';

Both plans say SCAN customers before any index. The counts are 111 and 199. After creating an index on name, both plans still scan, now as a covering index scan:

SCAN customers USING COVERING INDEX idx_customers_name

No SEARCH appears for either query, not even the prefix pattern. Two reasons, and both matter. First, a leading wildcard (%42%) can never use a B-tree index, because the index is ordered by the start of the value and the query does not know the start. Second, on a 5,000-row table the planner judged a full index scan cheaper than a SEARCH, even for the prefix pattern. An index is a suggestion the planner can decline, and on small tables it often does. If you add an index and the plan does not change, the index did not fail. The planner measured and disagreed with you.

The verdict

Five checks, five plan readings, and only four of them ended in an index. The point lookup, the range count, and the sort all changed from SCAN or temp sort to SEARCH or index walk, with speedups of roughly 40-fold to 350-fold on this dataset. The join plan confirmed the right shape without a new index. The LIKE check refused to cooperate, which is the most useful result in the article: it proves the plan, not the index, is the authority.

Run these five reads on your slowest query before you rewrite it: point lookup, range filter, join shape, sort column, and any LIKE pattern. Write down the plan first, change one thing, and read the plan again. If the plan did not change, neither did the query, whatever your intuition says.

Key takeaways

1. SCAN means a full table walk; SEARCH means an index jump. That one-word difference is usually the whole diagnosis.
2. A point lookup without an index scanned 100,000 rows in 5.09ms; with one index it searched in 0.02ms.
3. A covering index answers a query from the index alone, with no table-row fetches: 5.57ms down to 0.14ms on a range count.
4. A good join plan scans the small side and searches the big side by the join key. SCAN on both sides means a missing index.
5. LIMIT does not save a query that sorts first. An index on the sort column turned a 10.34ms temp sort into a 0.03ms index walk.
6. An index is a suggestion the planner can decline. A leading-wildcard LIKE never uses a B-tree index, and on a small table even a prefix LIKE may still scan.
7. The rule that ties them together: read the plan, change one thing, read the plan again. Keep an index only if the plan improves.

What is the slowest query you own right now, and what does its plan say in its first line? Run EXPLAIN QUERY PLAN on it before you touch the SQL, and bring the answer to that question.

Related on Everyday Data Science

• Five JOIN Traps That Quietly Rewrite Your Numbers. One Orders Table Reported $620, $1,240 and $2,040.
• Five Aggregation Traps That Rewrite Your Totals. I Measured Each One on Twelve Rows.
• Five Date Traps That Move Money Between Months: I Measured Each One

Sources: SQLite EXPLAIN QUERY PLAN documentation · SQLite query planning documentation · SQLite CREATE INDEX documentation