Indexes in Practice — When They're Used (and Ignored)

An index exists — so why is the query still slow?

Having an index doesn't mean the optimizer uses it. The single most useful debugging skill here is knowing when an index applies and when it's silently skipped. The mental model: a B+tree index is a phone book sorted by its columns — you can only seek on a prefix of that sort order.

The leftmost-prefix rule (composite indexes)

An index on (a, b, c) can serve a, a AND b, and a AND b AND c — but never b or c alone, because the data is sorted by a first. Order the columns by what you filter on most (and put equality columns before range columns).

When an index is IGNORED

Query	Why the index is skipped	Fix
WHERE LOWER(email)=?	function on the column — the index is on email, not LOWER(email)	functional index on LOWER(email)
WHERE name LIKE '%son'	leading wildcard — can't seek a prefix	trailing wildcard, or a trigram/full-text index
WHERE id = '42' (id is int)	implicit type cast disables the index	match the column type
WHERE status = 'active' (90% of rows)	low selectivity — a seq scan is cheaper than millions of index hops	nothing — the optimizer is right

Covering index = index-only scan

If the index contains every column the query needs, the DB answers from the index alone and never touches the table (no bookmark hop). E.g. for SELECT total FROM orders WHERE user_id=?, an index on (user_id, total) is covering.

EXPLAIN SELECT total FROM orders WHERE user_id = 7;
--  Index Only Scan using orders_user_total on orders   <- covering, no table access
-- vs Index Scan  (then a heap fetch per row)  vs  Seq Scan  (no usable index)

Pitfalls

• Over-indexing: each index slows every write and costs storage. Index for real query patterns.
• Wrong column order in a composite index makes it useless for your actual filters.
• Trusting it works — always confirm with EXPLAIN (next page).

Takeaways

• Leftmost-prefix: (a,b,c) serves prefixes only; order columns by your filters (equality before range).
• Functions, leading wildcards, type casts, and low selectivity all defeat an index.
• A covering index answers from the index alone — the fastest read.

---

Re-authored for this guide; phone-book/leftmost-prefix diagram hand-authored as SVG. Follows Use The Index, Luke! (Markus Winand). See also: How Indexes Work, How a Query Executes.

I just read a lesson on **Indexes in Practice — When They're Used (and Ignored)** (Databases) and want to truly understand it. Explain Indexes in Practice — When They're Used (and Ignored) from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Indexes in Practice — When They're Used (and Ignored)** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Indexes in Practice — When They're Used (and Ignored)** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Indexes in Practice — When They're Used (and Ignored)** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.