Understanding database indexes: the missing mental model

Most tutorials explain indexes as "they make queries faster." That's like explaining a car as "it goes places." Here's the mental model I wish I had earlier.

An Index Is a Sorted Copy

Imagine a phone book. Without an index, finding "Nguyen" means scanning every page. With an alphabetical index, you jump straight to N.

A database index works the same way: it's a sorted copy of specific columns, with pointers back to the full row.

CREATE INDEX idx_users_email ON users(email);

This creates a B-tree sorted by email. Looking up WHERE email = 'x@y.com' goes from O(n) full scan to O(log n) tree traversal.

What Actually Happens

Without index:

Table scan: row 1 → row 2 → ... → row 1,000,000
Checks every row. Slow.

With index:

B-tree lookup: root → branch → leaf → pointer → row
~3-4 disk reads for millions of rows.

The Cost

Indexes aren't free:

1. Disk space: Each index is a copy of those columns
2. 2. Write overhead: Every INSERT/UPDATE/DELETE must update the index too
3. 3. Maintenance: Fragmentation over time

Rule of thumb: index columns you query by, not columns you just store.

Composite Indexes

CREATE INDEX idx_orders_user_status
ON orders(user_id, status);

This index helps:

• WHERE user_id = 5 (uses first column)
• • WHERE user_id = 5 AND status = 'pending' (uses both)

This index does NOT help:

• WHERE status = 'pending' (can't skip first column)

Think of it like a phone book sorted by last name, then first name. You can find all "Nguyen", or "Nguyen Van", but not all "Van" efficiently.

This is called the leftmost prefix rule.

Covering Indexes

CREATE INDEX idx_orders_cover
ON orders(user_id, status, total);

SELECT status, total FROM orders WHERE user_id = 5;

All needed columns are IN the index. The database never touches the table — it reads everything from the index. This is called an index-only scan and it's the fastest possible query.

When NOT to Index

• Low-cardinality columns: A boolean is_active with 50/50 distribution — the index doesn't help because it still reads half the table
• • Small tables: Under ~1000 rows, a full scan is fast enough
• • Write-heavy tables: Every index slows writes. If you INSERT 10K rows/second, think carefully about each index
• • Columns you never filter by: An index on bio text that you only display is wasted

How to Check

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 5;

Look for:

• ✅ Index Scan or Index Only Scan
• • ❌ Seq Scan on large tables
• • ❌ Bitmap Heap Scan with high rows (index exists but isn't selective enough)

What's the most impactful index you've added? One well-placed index can turn a 30-second query into 5ms.