Why a Database Index Makes Queries Fast

Without an index

For WHERE email = ? without an index, the database scans every row. At scale that is a disaster. An index is a directory for the column, taking lookup from O(n) to O(log n).

B+tree index

Most relational indexes are B+trees: ordered, short and wide, with linked leaves. Equality seeks down the tree; range scans read along the leaf chain -- both fast.

Two key points

1. Return to table: a secondary index stores only the indexed columns and the primary key; you then look up the rest in the main table. A covering index (it already has the needed columns) avoids this;
2. Leftmost prefix: a composite index (a, b, c) is used from the left only, so WHERE b = ? cannot use it.

More is not always better

• Slower writes: every insert or update maintains the index;
• Space: indexes are data too;
• Wrong column: a low-cardinality column like gender gains almost nothing.

Real-world cases: three "I added an index and it is still slow"

1. Composite index in the wrong order: the index is (status, created_at) but the query filters on created_at alone, so the leftmost prefix cannot apply — as if no index existed. Order the columns to match real queries, highest selectivity first.
2. Implicit type conversion: user_id is a string column but the query is WHERE user_id = 123 (a number). The database converts and cannot use the index. Match parameter types to the column definition.
3. A function wrapping the indexed column: WHERE DATE(created_at) = '2026-01-01' defeats the index; rewrite it as a range: created_at >= ? AND created_at < ?.

FAQ

What should I look for in EXPLAIN? Whether a full scan occurs, which key is used, and the estimated rows; type=ALL usually means no index. Can an index replace sorting? Yes — when the ORDER BY columns match the index order, the extra sort disappears. When should I build a covering index? When a hot query touches only a few columns and you want to avoid returning to the table; the cost is a larger index and slower writes. Why not index every column? Indexes are maintained on every write, so more of them clearly degrades write performance and space — index only what queries actually use.

Index design and rollout process

The risk of index changes is routinely underestimated; follow a fixed process:

1. Start from slow query logs: index what the evidence shows, not what you assume gets queried;
2. Validate at realistic volume: small tables are fast without indexes, so measure the gain where the data is large;
3. Account for write cost: every index slows writes, so be strict about how many a write-heavy table carries;
4. Add online on large tables: MySQL supports online DDL, but still avoid peak hours and watch for locks and replication lag;
5. Verify afterwards: confirm the target query's plan actually uses the new index — "added an index, no effect" is the common outcome.

Dropping unused indexes matters just as much: accumulated redundancy keeps costing write throughput and storage, so review index usage statistics quarterly.

Read-heavy versus write-heavy tables

1. Write-heavy strategy: logs, events and ledgers write constantly, so keep indexes to the few the queries genuinely need and handle the rest with async aggregation or offline analysis.
2. Read-heavy can be more generous: query-dominant tables can carry several composite indexes, but still prune those never used.
3. Avoid schema changes at peak: add indexes and alter columns off-peak, and assess replication lag first so the primary does not slow its replicas.
4. Partition large tables: when a single table hits a wall, partitioning by time or business dimension narrows scans and turns archiving into a partition operation.
5. Mind cache hit ratio: an index helps only if it stays in memory; once indexes plus data exceed it, random reads become disk I/O, so revisit the data model or separate hot from cold.

The goal is not making every query use an index, but keeping critical query latency stable at an acceptable write cost.