A query that ran instantly on a thousand rows can crawl on a million. The usual culprit, and the usual fix, is indexing. Indexes are the single most important tool for database performance, yet they are widely misunderstood — both under-used and over-used. This guide explains the trade-off.
The book analogy
Imagine finding every mention of a word in a book with no index — you would read every page. That is a full table scan. A book’s index lets you jump straight to the right pages. A database index does the same: it is a separate, sorted structure that lets the database locate matching rows without examining every one.
When an index helps
Indexes dramatically speed up queries that filter, sort or join on the indexed column. If you frequently look up users by email, an index on email turns a scan of the whole table into a near-instant lookup. The bigger the table, the more dramatic the difference — which is why problems often appear only as data grows.
The cost of indexes
Indexes are not free. Every index must be updated whenever you insert, update or delete a row, so they slow down writes and consume storage. An index the database never uses is pure overhead. This is why "just index everything" is wrong: each index should earn its place by speeding up real queries.
Practical guidance
- Index columns you frequently filter, join or sort on.
- Primary keys are indexed automatically; foreign keys usually deserve one too.
- Avoid indexing columns with very few distinct values, where a scan is just as fast.
- Use your database’s query plan tool to see whether an index is actually used.
The takeaway
Indexing is a balance: faster reads in exchange for slower writes and more storage. Add indexes deliberately, based on the queries you actually run, and verify they are used. Done well, indexing is the difference between a snappy application and one that grinds to a halt as it grows.