Why Indexes Make Queries Faster
Adding an index is the most common performance fix in databases, and it can feel like magic: a query that took seconds now returns instantly. It is not magic. An index is a separate, ordered data structure that lets the database find rows without scanning the whole table - the same reason a book's index lets you find a topic without reading every page. Understanding the mechanism tells you exactly when an index will help and, just as importantly, when it will not.
Without an index: the full scan
When you query a column with no index, the database has no choice but to read every row and check the condition. This is a full table scan. On a thousand-row table it is instant; on a hundred-million-row table it is a disaster. The cost of a scan grows linearly with the table size, so a query that was fine in development can fall off a cliff in production as data accumulates.
What an index actually is
Most indexes are B-tree structures: a balanced tree that keeps keys in sorted order. Because the keys are sorted and the tree is shallow, the database can find any value in a number of steps proportional to the logarithm of the row count, not the row count itself. Finding one row among a hundred million takes only a handful of steps instead of a hundred million comparisons.
| Rows | Full scan comparisons | B-tree lookups (approx) |
|---|---|---|
| 1,000 | 1,000 | ~10 |
| 1,000,000 | 1,000,000 | ~20 |
| 1,000,000,000 | 1,000,000,000 | ~30 |
The index points back to the row
An index does not usually contain the whole row - it contains the indexed column values plus a pointer back to the full row. The database walks the index to find matching entries, then follows the pointers to fetch the rows. If the index already contains every column the query needs - a covering index - it can skip that second step entirely and answer from the index alone.
-- Slow on a large table: full scan of every row.
SELECT * FROM users WHERE email = 'a@b.com';
-- Create an index on the searched column.
CREATE INDEX idx_users_email ON users(email);
-- Now the database seeks directly via the B-tree
-- instead of scanning the whole table.Indexes are not free
Every index is a copy of some data that must be kept in sync. On every insert, update, or delete, the database must also update the relevant indexes. That means indexes speed up reads but slow down writes, and they consume storage and memory. A table with ten indexes pays that maintenance cost ten times on every write. Over-indexing a write-heavy table can be as harmful as under-indexing a read-heavy one.
| Operation | Effect of adding an index |
|---|---|
| Matching reads | Faster - seeks instead of scans |
| Inserts / updates / deletes | Slower - index must be maintained |
| Storage | More - the index is extra stored data |
| Non-matching reads | No benefit - the cost is pure overhead |
When an index will not help
- Low-selectivity columns: indexing a boolean or a status with two values rarely helps, since a scan is nearly as fast.
- Queries that transform the column: wrapping an indexed column in a function usually prevents the index from being used.
- Small tables: the scan is already fast, so the index only adds write overhead.
- Leading-column mismatch: a composite index helps only when the query filters on its leading columns in order.
Indexes also underpin efficient ordering, which is why they are essential for fast keyset pagination - the seek that makes deep pages cheap depends on an index over the ordering columns.
Practical guidance
- Index the columns you actually filter, join, and order by - not every column, hopefully.
- Use the query planner's EXPLAIN output to confirm an index is actually being used before trusting it.
- Favor selective columns; an index on a column with mostly distinct values pays off the most.
- Watch write-heavy tables - drop indexes that are never used, since they only add cost.
Conclusion
Indexes make queries faster by replacing a linear scan with a logarithmic lookup through an ordered structure. That speed is bought with write overhead and storage, so an index is a deliberate trade, not a free upgrade. Index the columns your queries actually use, verify the planner uses them, and prune the ones that only cost you. Done well, indexing is the single highest-leverage tool for database performance.