CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 4 of 5 · 1 min read

A first-pass rule for what's worth indexing

A practical starting heuristic, before Level 5's deeper treatment.

A first-pass rule for what's worth indexing

Think of deciding whether a book needs an index at all. A short pamphlet probably doesn't need one. A thousand-page reference almost certainly does.

In plain words: a column is usually worth indexing if queries frequently filter or join on it, and the table has enough rows that scanning all of them is actually slow. A column rarely filtered on, or a tiny table scanned in a few milliseconds anyway, usually does not need an index. Every index also has a real cost on writes, covered properly in Level 5's module on when indexes hurt.

A worked example. orders.customer_id, filtered on constantly and holding thousands of rows, is a strong candidate. A small tiers lookup table with five rows is not, regardless of how often it is queried.

The key lines. No new code here; this part is a judgment call, not a mechanism.

Remember this

This is a first-pass rule only. Level 5 covers the real data structure behind an index, when a composite index's column order should be chosen deliberately, and the real cost an index adds to every write. Treat this page's rule as a starting intuition, not the final word.

Quick notes

  • CREATE INDEX builds a structure speeding up lookups on the indexed column.
  • A composite index helps queries filtering on a leading prefix of its columns, not a later column alone.
  • A column frequently filtered or joined on, in a large enough table, is usually worth indexing.
  • A rarely filtered column, or a small table, usually does not need one.
  • Level 5 covers an index's real internal structure and its write-time costs in full depth.