Database Indexing Explained: Why Queries Are Slow (2026)

The query that got slower every week

A query returns in 8 ms in development, where the table has 5,000 rows. In production, with ten million rows, the same query takes 4 seconds. The code is identical. The database engine is identical. The query is identical.

The difference is that your database is reading every single row to answer it. At 5,000 rows nobody notices. At ten million, it is a production incident — and the graph looks like a slow leak, because the pain grows with your success.

An index is what turns "read every row" into "jump straight to the row".

The analogy: the back of the textbook

You need every mention of "polymorphism" in a 900-page book. Without an index you start at page 1 and read to page 900. That is a full table scan.

With the index at the back, you flip to P, find "polymorphism — 412, 418, 533", and turn to three pages. Three lookups instead of 900.

The analogy carries further than most people expect. The index takes up extra pages in the book — indexes cost storage. Adding a chapter means reprinting the index — every write has to update the indexes too. And an index on the wrong term is useless — an index on "the" would list every page and save you nothing.

What an index actually is

Almost every relational index is a B-tree (specifically a B+ tree). Two properties matter for interviews:

• It stays balanced and shallow. Even at 100 million rows a B-tree is typically 3–4 levels deep, so a lookup is a handful of page reads instead of millions. Scans grow linearly with table size; index lookups grow logarithmically — which is why the gap widens as you grow.
• The leaves are sorted and linked. That is why one index serves equality (WHERE id = 42), ranges (WHERE created_at > '2026-01-01') and ORDER BY — the data is already in order, so sorting is free.

Hash indexes exist and are marginally faster for exact matches, but they cannot do ranges or ordering at all, which is why B-trees are the default everywhere.

The rule that decides whether your index is used

Composite indexes are where most real confusion lives. Given:

INDEX (country, city, age)

Think of it as sorting a phone book by country, then city, then age. The leftmost prefix rule follows directly:

• WHERE country = 'IN' — uses the index.
• WHERE country = 'IN' AND city = 'Pune' — uses the index.
• WHERE country = 'IN' AND city = 'Pune' AND age = 30 — uses all three columns.
• WHERE city = 'Pune' — cannot use it. You are asking for every city called Pune without knowing the country, which is the phone book sorted by country being no help at all.

This single rule explains a large share of "I added an index and nothing got faster" reports. Column order in a composite index is a design decision, not a formality.

Want to master this with video lessons and real case studies? This topic is covered in depth in my Udemy course System Design Fundamentals for Interviews — 5.5 hours, rated 4.8★, built from real interview questions.

Covering indexes: skipping the table entirely

Normally an index lookup finds a pointer, then the database reads the actual row to get the remaining columns. That second step costs a random disk read per row.

If the index already contains every column the query needs, the database can answer from the index alone and never touch the table. That is a covering index, and it is often the difference between 200 ms and 5 ms on a query that was already using an index.

Concretely: for SELECT city FROM users WHERE country = 'IN', an index on (country, city) covers it. An index on (country) alone does not.

The mess: indexing everything

The obvious conclusion from all this is "index every column". It is wrong, and it is wrong in a way that hurts quietly.

Every index must be updated on every INSERT, UPDATE and DELETE. A table with eight indexes turns one insert into nine write operations. Write throughput collapses while every read looks great in testing — so the regression lands in production, under load, on the path nobody profiled.

Indexes also consume storage and memory. An index that is never used is pure cost: it slows writes, occupies RAM that could have cached useful data, and gives you nothing back.

The fix: index for your actual queries

• Index columns that appear in WHERE, JOIN and ORDER BY — not columns you merely SELECT.
• Prefer high-cardinality columns. An index on a boolean matches half the table and the planner will ignore it in favour of a scan.
• Put the most selective column first in a composite index, subject to the leftmost prefix rule matching your real query shapes.
• Read the query plan — EXPLAIN in Postgres and MySQL — and confirm the index is actually used. Assumption is not evidence here.
• Drop unused indexes. Both engines expose index usage statistics; an index with zero scans after a month is costing you writes for nothing.

Quick check

You have INDEX (user_id, created_at). Which of these use it?

A. WHERE user_id = 5 ORDER BY created_at DESC
B. WHERE created_at > '2026-01-01'
C. WHERE user_id = 5 AND created_at > '2026-01-01'

(Think about it before reading on.)

A and C use it. B cannot. A is the ideal case — it filters on the leading column and gets the sort for free, because rows for that user are already stored in created_at order. C uses both columns as a range on the second. B skips the leading column, so the leftmost prefix rule rules it out; it needs its own index on created_at.

How to talk about indexing in an interview

"The query filters on user_id and sorts by created_at, so I'd add a composite index on (user_id, created_at) — the leading column does the filtering and the second gives me the ordering without a sort. If the query only needs a couple of columns I'd extend it into a covering index so we never touch the heap. I wouldn't index every column: each index adds write amplification, and eight indexes turns one insert into nine writes. I'd confirm with EXPLAIN rather than assuming, and drop indexes with no recorded scans."

Indexing pairs directly with the storage decisions in replication and sharding: an index makes a single node fast, replication spreads the reads, and sharding is what you reach for when neither is enough. Reaching for sharding before you have checked your indexes is one of the more expensive mistakes a team can make — and interviewers notice the order you propose them in.

Key Takeaways

• Without an index the database reads every row; the cost grows linearly with your success.
• B-tree indexes stay shallow and sorted, which is why they serve lookups, ranges and ORDER BY.
• The leftmost prefix rule decides whether a composite index is usable — column order matters.
• A covering index answers the query from the index alone and skips the table read.
• Every index slows every write; unused indexes are pure cost.
• Verify with EXPLAIN instead of assuming the planner does what you expect.

Next Steps

Indexes make a single database fast. When one machine can no longer hold the writes, the next question is how to split the data — and that is where consistent hashing decides how much data has to move every time you add a node.