Why Indexes Are Fast — and Why They Stop Working

· tech

#sql#performance

📑 Contents

A change of topic: the engine and performance. The first thing to understand is indexes — why adding one makes a query hundreds of times faster, and why sometimes you add one and it seems to do nothing. The answer to both questions hides in its data structure: the B-tree.

No index vs index: full scan vs direct hit

Without an index, a query like WHERE id = 500 can only do a Seq Scan (full table scan) — start at the first row and compare one by one until it’s found. With an index, the database maintains an extra sorted B-tree, and the lookup becomes a few steps down from the root:

No index: Seq Scan (full table) row 1 row 2 row 3 row 4 row 5 ← target row 6 scan every row to find it → O(n), slower as data grows With an index: B-tree root node node leaf leaf target leaf a few steps down the tree → O(log n)
An index is like a sorted table of contents: no page-by-page flipping, just binary-search your way in. And because the leaves are ordered, range queries (>, BETWEEN), ORDER BY and prefix LIKE 'abc%' all benefit too — find the starting point, then walk along the leaves

Remember it in one sentence: no index means “search row by row”; an index means “home in using a sorted structure”. That’s also why an index speeds up more than = — ranges and sorting benefit too, because the B-tree’s leaves are themselves sorted.

Indexes aren’t free

If they’re this fast, why not index every column? Because an index trades space for time, and it slows writes: every INSERT / UPDATE / DELETE has to maintain every relevant index at the same time (inserting the new value into the right place in that sorted tree). More indexes means slower writes and more space. So indexes go where they cut — columns often used to filter in WHERE, to JOIN, to ORDER BY — not mindlessly on every column. A read-heavy, write-light table deserves more indexes; a write-hot table calls for restraint.

Composite indexes: the leftmost prefix

One index can cover several columns (a composite index), but it has a rule you must understand: the leftmost prefix — it can only be used starting from the leftmost column, contiguously:

INDEX (last_name, first_name) data sorted by last_name → first_name (like a phone book) ✓ WHERE last_name = 'Wang' flip to the "Wang" section → index used ✓ WHERE last_name = 'Wang' AND first_name = 'An' last name then first name, pinpointed → index used ✗ WHERE first_name = 'An' (skips last_name) only the first name: can't locate it anywhere in the book → index unusable
A composite index is like a phone book sorted "last name → first name": knowing the last name lets you flip there, last + first pinpoints it; but given only the "first name", you'd have to read the whole book — the index can't help. So the column order in a composite index decides which queries it can serve

When an index quietly stops working

The biggest trap with indexes is that they often end up “added, but never used” — with no error. The most common situations that disable one:

  • Wrapping the column in a function or arithmetic: WHERE DATE(created_at) = '2026-07-11' or WHERE amount * 2 > 100 — the index stores the column’s raw value, not the value of DATE(...) or amount*2, so it can’t be used. Rewrite so the column stands alone on one side (WHERE created_at >= '2026-07-11' AND created_at < '2026-07-12').
  • A leading wildcard: LIKE '%abc' can’t use the index (no idea which letter to start from), but LIKE 'abc%' can.
  • Implicit conversion from a type mismatch: the column is a string but you write WHERE phone = 0912345678 (a number), and the database may be forced to cast and give up the index.
  • Selectivity too low: a column like “gender” with only two values barely narrows the rows, and the optimizer may prefer a full scan — an index only pays off on columns that “drastically narrow the range”.

One more advanced but practical concept: a covering index — if the columns the query needs all happen to be in the index, the database needn’t go back to the table at all (an index-only scan), faster still.

So how do you confirm whether an index is actually being used? Look at EXPLAIN — which is exactly the next post’s subject, the counterpart to the Spark execution-plan post: whether the plan says Index Scan or Seq Scan is plain to see.

Reflections

An index trades space for time — no free lunch

I’ve seen plenty of people reflexively add an index whenever a query is slow, until writes became painfully slow and nobody knew why. An index’s speed-up has a price — it takes space and slows every write. So now, when I see “slow query → add index”, I always ask one more thing: is this table read-heavy or write-heavy? Is this query really frequent enough to be worth growing a tree for? Adding an index isn’t a free optimisation, it’s an investment to be costed — like whether Type 2 keeps history, like any engineering trade-off, the key is seeing clearly what you’re trading for what.

”Don’t wrap the column in a function” is a rule that crosses tools

WHERE func(col) disables the index for a simple reason: the index stores col’s raw value, not func(col)’s. Interestingly, this is the same thing as Spark’s filter pushdown being blocked by a function — wrap the column in arithmetic and the optimizer can’t use “the column’s original form” to speed things up. So I’ve built a habit: when writing filter conditions, let the column stand alone on one side as far as possible (col >= x rather than func(col) = y), and move the arithmetic to the constant side. That small habit gives indexes and pushdowns the chance to kick in — how you write a condition directly decides whether the engine can help you.

The leftmost prefix forces you to think about “the shape of your queries” first

The composite index’s leftmost-prefix rule looks like a limitation on the surface, but in practice it forces you to think about something more important: how do I actually query this table? An index isn’t built for “the table”, it’s built for “the query pattern” — the column order depends on which columns you most often filter together and which is most selective. That makes me inventory the actual queries before building an index, rather than combining a few columns by feel. Understand how you query before you decide how to build — the line fits indexes best, and it holds for every design done for performance.