indexing
Index Anti-patterns
Recognize patterns that silently disable indexes — implicit casts, leading wildcards, OR conditions, functions on columns.
An index exists but the query is still slow — a common and frustrating situation that almost always traces back to writing the query in a way that prevents the optimiser from using the index. These anti-patterns are silent: nothing errors, the result is correct, and only an EXPLAIN reveals that the database fell back to a full scan. Recognising them is among the highest-value SQL skills.
A phone book sorted by last name; if you only know "Sm-something at the start", a wildcard search forces flipping through every page.
Key Concepts
1
The recurring theme is that an index on a column is only usable if the query leaves that column "bare" on one side of the comparison. Wrapping it in a function or arithmetic — WHERE YEAR(created_at) = 2024 or WHERE price * 1.1 > 100 — forces the database to compute the expression for every row, defeating the index; the fix is to rewrite the predicate to leave the column alone (created_at >= '2024-01-01' AND created_at < '2025-01-01') or to build a matching functional/expression index. An implicit type cast does the same damage: comparing an indexed VARCHAR column to a number makes the database convert every row. A leading wildcard (LIKE '%term') cannot use a B-tree because the unknown prefix destroys the sort order. OR across different columns often prevents a single index from applying (a UNION of two indexable queries can be faster). And a low-selectivity predicate — one matching most rows, like a boolean flag — is one the optimiser will rightly ignore the index for.
WHERE YEAR(created_at) = 2024WHERE price * 1.1 > 100created_at >= '2024-01-01' AND created_at < '2025-01-01'VARCHARLIKE '%term'
2
The way to diagnose all of these is the same: run EXPLAIN and look for a sequential or full table scan where you expected an index seek. The mental model worth stating in an interview is "keep the indexed column bare on one side of the operator, and make sure the predicate is selective" — most index anti-patterns are a violation of one of those two ideas.
EXPLAIN