DEV Community

EME GUG
EME GUG

Posted on

Understanding database indexes: the missing mental model

#sql #database #performance #backend

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);
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode

With index:

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

The Cost

Indexes aren't free:

  1. Disk space: Each index is a copy of those columns
  2. Write overhead: Every INSERT/UPDATE/DELETE must update the index too
  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);
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (1)

Collapse
 
systemcraftdev profile image
SystemCraftDev •

Good mental model. Worth extending the "low-cardinality columns" rule though - it's really about selectivity for the query you're running, not raw cardinality. A boolean split 50/50 is a bad index on its own, but a partial index like CREATE INDEX idx_active_users ON users(id) WHERE is_active = true can still be very effective if most of your queries only care about the true rows - you're indexing the selective subset, not the whole low-cardinality column.

The same composite-index ordering logic extends to ORDER BY too: if a query's ORDER BY matches the trailing columns of the index (after any WHERE-equality columns), the database can skip a separate sort step entirely - often as big a win as avoiding the scan in the first place.