Why indexes matter

An index is the single biggest performance lever in most databases. A query that takes ten seconds without one takes a millisecond with one. Knowing how indexes work lets you say, in an interview, exactly which queries your design makes fast and what that costs.

The problem: scanning

Rows are stored in pages on disk, in roughly the order they were inserted. To answer WHERE email = 'ana@example.com' without help, the database reads every page and checks every row: a full table scan.

Scans are fine for small tables and for reports that genuinely need every row. For any frequent query on a large table, they are the bug.

The B-tree

The default index in almost every relational database is a B-tree (strictly, a B+ tree). Think of it as a sorted, very wide tree:

• Each page holds hundreds of sorted keys with pointers to the next level.
• Leaves hold the indexed values with a pointer to the full row (or, in some databases, the row itself), and are linked in order.
• A lookup walks from the root to a leaf: three or four page reads, even for billions of rows. The upper levels are small and stay cached in memory.

Because leaves are sorted and linked, B-trees also answer range queries (created_at BETWEEN …), prefix matches (LIKE 'ana%') and ORDER BY ... LIMIT without sorting.

What indexes cost

Every index is a second copy of some of your data, kept in order.

• Writes get slower. Each insert, update of an indexed column, or delete must also update every affected index. A table with ten indexes does roughly ten extra writes per insert.
• Space and memory. Indexes take disk, and they are fastest when their hot parts fit in RAM, competing with your data for cache.
• Planning. The query planner has more choices, and occasionally picks badly.

So the rule is: index for the queries you run often, especially those in latency-sensitive paths. Primary keys and unique constraints get indexes automatically; foreign keys usually deserve one too, because joins and cascading deletes look them up.

Indexes and uniqueness

A unique index both speeds up lookups and enforces a rule: no two rows may share the value. This is how a URL shortener guarantees no two links get the same code, and how signups guarantee unique usernames, even with concurrent requests on many servers.