~/learn/SQL · Query performance
How indexes work
Why one query is instant and its twin takes a minute.
why this matters
The difference between a query returning instantly and timing out is usually a single index. Knowing why earns senior marks in interviews.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
Without an index the engine reads every row — a full scan. An index is a sorted structure on one or more columns that lets the engine jump straight to the matching rows, the same way a book index beats reading every page.
Index the columns you filter and join on, not the ones you only display. A composite index on (a, b) also serves lookups on a alone, but not on b alone — column order matters.
Indexes are not free: every INSERT, UPDATE and DELETE has to maintain them, and they take disk space. Index selectively rather than everywhere.
Note
Low-selectivity columns (a status with three values) rarely benefit from their own index — the engine often decides a scan is cheaper.
Why it is so much faster
no index → scan all 10,000,000 rows index → jump through a sorted tree, ~23 steps
Common gotcha
Wrapping an indexed column in a function — WHERE upper(email) = … — stops the index being used. Compare the raw column instead.
Pro tip
On a composite index (a, b) the leftmost column rules: it helps filters on a, and on a+b, but not on b alone.
key takeaways
- An index is a sorted lookup structure, not a copy of the table.
- Functions on the filtered column disable the index.
- Composite indexes work left to right.
check your understanding
You have an index on (country, city). Which query cannot use it?
free practice
Build confidence with a question you can solve without Pro.