How Database Indexes Actually Speed Up Reads (And When They Don’t)

Indexes are the most misunderstood performance tool in databases. Everyone knows they help reads. Fewer people know when they do nothing or even make things worse.

This article is a clean mental model for how indexes actually work and how to reason about them.

The Core Idea

An index is an ordered data structure that helps the database avoid scanning the entire table. Most relational databases use B+ trees, which make range scans efficient.

If your query can use an index, the database reads only the relevant part of the tree instead of scanning rows linearly.

B+ Tree in One Paragraph

• Keys are sorted.
• Internal nodes guide you to leaf nodes.
• Leaves store sorted key ranges and point to row locations.

This means:

• Point lookups are $O(\log n)$.
• Range scans are sequential across leaves.

Selectivity: The Hidden Rule

Indexes only help if they reduce the number of rows scanned.

If a column is low-selectivity (like a boolean), the query planner may ignore the index because scanning many rows is not worth the overhead.

When Indexes Hurt

• High write volume: every insert or update must update the index.
• Low selectivity: scanning the index can cost more than a table scan.
• Wrong order: multi-column indexes only help if the query matches the prefix.

Example:

Index on (country, city) helps:

• WHERE country = ‘IN’
• WHERE country = ‘IN’ AND city = ‘Delhi’

But not:

• WHERE city = ‘Delhi’

Covering Indexes (Fastest Case)

If all columns in the query exist inside the index, the database can respond without touching the base table. These are covering indexes, and they are often the fastest path.

A Simple Index Tuning Flow

1. Identify slow queries.
2. Check EXPLAIN plan.
3. Add or adjust indexes based on selectivity.
4. Re-check write overhead.

Final Thought

Indexes are not magic. They are a trade-off: faster reads, slower writes. The key is to measure where your system hurts and tune accordingly.