Indexes and the Query Plan

1A second copy, sorted differently

Strip away the vocabulary and an index is a sorted list of values, each with a pointer back to its row. That single sentence explains everything an index can and cannot do.

Because it is sorted, the engine can find a value by bisection rather than by reading everything — which is why lookups go from proportional to the table size down to proportional to its logarithm. In practical terms, finding one row among fifty thousand takes about sixteen comparisons rather than fifty thousand.

Because it is sorted by particular columns in a particular order, it can only help with questions whose answer lives in that order. And because it is a second copy, every insert, update and delete on the table must also update every index on it. That is the cost, it is paid on every write forever, and it is why the answer to a slow query is not always another index.

It is worth being concrete about the size of that cost, because “indexes slow down writes” is one of those true statements that gets repeated without a number attached and therefore never affects a decision. A row insert into an unindexed table is one append. With four indexes it is one append plus four ordered insertions, each of which has to find its position, may split a page, and produces its own write to durable storage. Measured on ordinary hardware that is typically somewhere between a 30% and a 300% increase in write cost depending on how random the indexed values are — an index on an ever-increasing id appends at the end and is nearly free, while an index on a random identifier touches a different page every time and is the expensive end of that range. The two indexes are the same feature; the distribution of the values is what makes one cheap and the other not.

There is a second cost that is easier to forget because it never appears in a benchmark: an index is a thing that has to stay true. Every schema migration has to consider it, every bulk import runs slower because of it, every restore takes longer to rebuild it, and every planner decision has one more candidate to weigh. None of that is fatal, and none of it is visible on the dashboard you would look at when deciding to add one.

2Stop guessing: ask the planner

Every relational database will explain its intended strategy before running a query. In SQLite the command is EXPLAIN QUERY PLAN, and the whole vocabulary is four words.

• SCAN — reading every row of a table. Fine on something small; the explanation for most slow queries otherwise.
• SEARCH … USING INDEX — seeking directly to the matching rows. This is what you wanted.
• USING COVERING INDEX — better still: every column the query needs is in the index, so the table is never touched at all.
• USE TEMP B-TREE FOR ORDER BY — the engine had to gather all the matching rows and sort them. The filter may have used an index and the sort did not, and on a large result this dominates.

The instrument below runs against 50,000 generated invoices. Toggle the index and watch both the plan and the rows-examined figure change.

Notice the third query. The index does not help it at all, because a GROUP BY over every row has to touch every row regardless. That is not a failure; it is the planner correctly declining to use an index that would make the query slower.

3Composite indexes, and why the order is not obvious

An index on several columns is sorted by the first, then the second within that, then the third. That nesting is the whole rule, and it decides which queries the index can serve.

Think of a telephone directory sorted by surname, then forename. You can find every Ashgrove instantly. You can find Ashgrove, Ada instantly. You cannot find every Ada, because the Adas are scattered through the whole book. An index on (surname, forename) serves a query on surname and a query on both, and does nothing for a query on forename alone. This is the leftmost-prefix rule.

Two consequences follow, and both are load-bearing:

1. Put the equality filters first, then the range or sort column. An index on (customer_id, status, due_on) can seek to one customer, then to one status within them, and then read the rows already in due_on order — so the sort is free. Reversing it to (due_on, customer_id, status) means seeking a date range first and then filtering every row in it, which is much worse.
2. An index on (a, b) makes a separate index on a redundant. The composite one already serves every query the single-column one served. Keeping both costs write speed for nothing, and this is the most common piece of dead weight in a mature schema.

4Covering indexes

If every column a query mentions is in the index, the engine never has to open the table. That is often the difference between fast and instant, and it is easy to miss because nothing about the query changes.

Normally the engine uses the index to find which rows match, then goes to the table to fetch the columns the query asked for. That second step is a separate read per row. When the index already carries those columns, the second step disappears and the plan says USING COVERING INDEX.

-- The query
SELECT customer_id, SUM(total_pence)
  FROM invoices
 WHERE status = 'paid'
 GROUP BY customer_id;

-- Serves the filter, then fetches each row from the table
CREATE INDEX i_a ON invoices (status);

-- Carries everything the query mentions: no table access,
-- and the rows already arrive grouped by customer_id
CREATE INDEX i_b ON invoices (status, customer_id, total_pence);

The second index is bigger and slower to write, and for a query that runs on every dashboard load that is usually a trade worth making. For a query that runs monthly it is not. This is the same reasoning as Chapter 6’s frequency column, arriving at the level of a single index.

5Four reasons your index is being ignored

You created it, the query is still slow, and the plan still says SCAN. There are four common explanations and they are all visible in the query text.

A function on the column. WHERE lower(email) = ? cannot use an index on email, because the index stores the original values and the query is asking about a derived one. The fix is an expression index — CREATE INDEX ON customers (lower(email)) — or storing the lowercase form. The same applies to WHERE date(created_at) = '2026-08-31', which is best rewritten as a range on the raw column.

A leading wildcard. LIKE '%grove' has no prefix to seek to, so a B-tree cannot help. LIKE 'Ash%' is fine and uses the index normally. This is exactly the case from Chapter 6 that wants a full-text index instead.

Low selectivity. If a column has four distinct values across fifty thousand rows, seeking to one of them still lands you in twelve thousand rows — and reading twelve thousand index entries and then twelve thousand table rows is slower than reading the table once in order. The planner knows this and declines, correctly. An index on a boolean is almost always dead weight, unless it is partial and the interesting value is rare.

The type does not match. Comparing a text column to a number, or a value the driver bound as a string to an integer column, can defeat the index silently. SQLite is unusually forgiving about types, which makes this quieter here than in Postgres, and no less real.

6N+1, seen from the query count

The most common database performance bug in application code is not a slow query. It is a fast query, run nine hundred times.

It happens when code fetches a list and then, for each item, fetches something related. One query for the list, plus one per row: hence N+1. Each individual query is indexed and takes two milliseconds, which is why nothing looks wrong in a slow-query log — and nine hundred of them at two milliseconds is one and a half seconds of pure round trips.

The reason an N+1 is so durable is that every instrument a team would reach for says the system is healthy. The slow-query log is empty, because the threshold is typically 100 ms or a second and each of these queries takes two. Average query latency is excellent, because the average is dragged down by nine hundred fast queries. CPU on the database is low. The only signal is a query rate that nobody has a baseline for, and an endpoint latency that the application team attributes to the database while the database team attributes it to the application. Both are half right, and the disagreement can last quarters.

What breaks the deadlock is measuring the thing that is actually wrong: queries per request. It is one counter, incremented in the database driver, reported alongside the response time. Once it exists, an N+1 stops being a mystery and becomes a number that went from six to nine hundred and one on a specific deploy. Teams that have this counter tend to go further and assert on it in tests — a test that loads a page with three records and one with thirty, and fails if the query count differs, catches the entire class of bug at the moment it is written rather than the month the data grows.

It is worth naming the version of this that agent-generated code produces, because it is subtly worse. Asked to add a field to a list endpoint, a coding agent will reliably reach for the relation that is already defined on the model, because that is the idiomatic and readable way to express it in every framework. The diff is two lines, it reads beautifully, and it turns one query into N+1. Nothing in the change looks like a performance decision, which is exactly why nobody reviews it as one. The defence is not to review harder; it is to have the query counter in the test suite, so that the shape of the change is caught by something that does not get tired.

✓Checkpoint

Five questions. Commit before you read the explanation.

▶Playground

Fifty thousand rows and no indexes at all. Create your own, run EXPLAIN QUERY PLAN, and try to make each of the queries below seek rather than scan.

✓Exercise set

Twelve problems against 12,000 real invoices. Most of them are graded on the plan rather than the rows — the tests read what SQLite decided to do, so a query that returns the right answer by scanning the whole table still fails. Your work is saved in this browser.