Indexes & Query Plans
Why one query is 2ms and the identical-looking one is 8 seconds — read the plan.
50 minDifficulty 3/5database · performanceAI-writtenWritten by a model on 16 August 2026 and not yet read by a person. Checked automatically: schema, the pedagogical rules the hand-written material is held to, and every diagram parsed for real.
Before this
Why this exists
Two queries look nearly identical — `WHERE email = 'x'` on one table, `WHERE bio = 'x'` on another — and one returns in 2 milliseconds while the other takes 8 seconds on the same size table. The difference isn't the query; it's that one column has an index and the other requires scanning every single row to find a match.
The mental model
A database table without an index on a column is like a phone book with no alphabetical order — finding one entry means reading every page. An index is a separate, pre-sorted data structure (typically a B-tree) that lets the database jump nearly directly to matching rows, the same way alphabetical order lets you flip straight to the right page of a phone book instead of reading it cover to cover.
How it works
Without an index, a filtered query does a full table scan
`SELECT * FROM users WHERE email = 'ada@example.com'` with no index on `email` forces the database to check EVERY row's email column, one at a time, until it finds matches — this is O(n) in the table's size, and it gets proportionally slower as the table grows, with no upper bound.
An index trades write speed and storage for read speed
Every INSERT, UPDATE, or DELETE on an indexed column has to also update the index's own structure, not just the row — this makes writes somewhat slower and consumes additional storage for the index itself. This is a genuine tradeoff, not a free lunch: indexing every column 'just in case' slows down every write for the benefit of reads that may never actually filter on those columns.
A query plan reveals whether an index is actually being used
`EXPLAIN` (or `EXPLAIN ANALYZE`) shows the database's actual execution strategy — an 'Index Scan' or 'Index Only Scan' means an index is being used; a 'Seq Scan' (sequential scan) on a large table is the red flag indicating a full table scan is happening, often because the needed index doesn't exist or the query is written in a way the optimizer can't use it.
A composite index's column order determines which queries it actually helps
An index on `(last_name, first_name)` efficiently supports `WHERE last_name = 'Lovelace'` AND `WHERE last_name = 'Lovelace' AND first_name = 'Ada'` — but it does NOT efficiently support `WHERE first_name = 'Ada'` alone, because the index is sorted by `last_name` first, and skipping straight to a specific `first_name` without knowing `last_name` requires scanning the whole structure anyway.
The mechanism
The query planner examines a query's filter conditions and checks whether an index exists that could satisfy them. If a suitable index exists, it navigates the index's sorted structure to locate matching rows directly, touching only a small fraction of the table. If no suitable index exists, it falls back to reading every row sequentially, checking each one against the filter condition.
flowchart TD
Q[WHERE email = 'x'] --> D{Index on email?}
D -->|yes| I[Index Scan: jump nearly directly to matching rows]
D -->|no| S[Seq Scan: check every row in the table]What people get wrong
- Adding an index to every column that's ever queried is a safe, purely beneficial practice.
- Every additional index slows down every write to that table (since the index must be updated too) and consumes storage — indexing columns that are rarely queried, or queried on a tiny table where a scan is already fast, adds cost with negligible benefit. Over-indexing is a real, common mistake that degrades write performance across the board while providing benefit for only a fraction of the added indexes.
- A primary key column doesn't need an explicit index, since it's already special.
- Most databases automatically create an index on the primary key specifically BECAUSE it's the primary key (to enforce uniqueness efficiently) — this isn't an exception to needing indexes, it's the same mechanism, applied automatically for exactly this one column. Understanding this clarifies that primary key lookups are fast for the SAME reason any other indexed lookup is fast, not because of some separate, unrelated optimization.
- A composite index on (A, B) is interchangeable with having two separate indexes, one on A and one on B.
- A composite index's usefulness for a given query depends heavily on COLUMN ORDER — it efficiently supports filtering on the first column alone or the first-and-second together, but generally NOT the second column alone, which two separate single-column indexes would each independently support. Choosing composite index column order incorrectly (or assuming it's equivalent to separate indexes) is a common source of queries that mysteriously don't benefit from an index that technically exists on the right columns.
When not to use it
- The table is small (a few hundred rows) and unlikely to grow significantly.
- A full scan on a small table is often already fast enough that adding an index provides negligible benefit while still costing write overhead — measure before assuming an index is needed.
- A column is written to very frequently and read from rarely.
- Consider carefully whether an index is worth it here — the write cost is paid on every single write, while the read benefit is realized rarely, which may not be a good trade for this specific column.
Terms
- B-tree
- — The most common index data structure, keeping keys in sorted order and enabling logarithmic-time lookups, range scans, and ordered traversal.
- Sequential scan (seq scan)
- — Reading every row of a table in order to find matches, used when no suitable index exists for a query's filter condition.
- Composite index
- — An index built on multiple columns together, whose usefulness for a given query depends on which columns are used and in what order they appear in the index.
- Query plan
- — The database's chosen strategy for executing a specific query, revealed via EXPLAIN, showing whether indexes are actually being used.
In an interview
A table has an index on (last_name, first_name). Why doesn't a query filtering only by first_name benefit from this index?
- the index is sorted primarily by last_name, then first_name within each last_name group
- without knowing last_name, first_name values are scattered throughout the index in no useful order for this query
- efficiently searching by first_name alone would require a separate index with first_name as (at least) the leading column
Can you recall it?
Why does adding an index speed up reads but slow down writes, and what does that mean for deciding which columns to index?