How database indexes make queries fast

Your database is not slow because it has too much data. It is slow because it might be looking through everything. Imagine trying to find one name in a book with 10,000 pages and no index: you would start at page 1, then page 2, then page 3, and keep going.

Without an index: the full scan

Data lives in blocks on disk, and each read costs time. Ask a table with no index for SELECT * FROM users WHERE id = 742, and the database has to do a full scan: read every block, one by one, until it finds a match. With 1,000 blocks on disk, finding id 742 this way can take 742 reads.

With an index: jump straight there

An index is a special structure the database keeps to quickly locate where the data lives. Think of it as a sorted map from a key, like the user ID, to the block that holds the row, kept separately from the table itself. A lookup follows the index in a few quick steps, narrowing the range each time, then goes straight to the right block. Same answer, but in this example it takes 3 reads instead of 742.

Same data. Same database. Way less searching.

That is why indexes can turn a painfully slow query into one that feels almost instant. Many databases store indexes as B-trees, which keep keys sorted so each step cuts the search down sharply.

The tradeoff

Indexes are not free. More indexes can mean:

• Faster reads, because lookups skip the scan.
• More storage, because each index is extra data to keep.
• Slower inserts and updates, because every index has to change when the data does.

Why it matters

An index is often the difference between a query that reads everything and one that jumps straight to the answer. But every index adds work to each write. So the goal is not to index everything. It is to index the right things: the columns your app actually searches, filters and sorts by.