Why does a function on a column stop an index from helping?
Define sargable predicates and the index you would add for a real filter.
Points worth saying
- An index is ordered by the stored value, not by a function of that value.
- WHERE YEAR(created_at) = 2026 cannot seek a plain created_at index.
- Rewrite the predicate as a range, then check the plan.
A full answer
A B-tree index stores keys in order. The engine can seek when the predicate preserves that order, such as a range or an equality on the leading column. If you wrap the column in a function, the stored order no longer matches the predicate, and the engine scans.
YEAR(created_at) = 2026 is the classic example. Rewrite it as created_at >= "2026-01-01" AND created_at < "2027-01-01". The index on created_at can seek that range.
Selectivity still matters. An index on a low-cardinality status column may be ignored if most rows match. A composite index should lead with the column you equality-filter, then the column you range-filter or sort.
Close the answer by saying you would read the actual plan, not guess. Look for a seek, the estimated rows, and whether a key lookup means the index should include the selected columns.
Example
SELECT id, total
FROM orders
WHERE created_at >= DATE '2026-01-01'
AND created_at < DATE '2027-01-01'
AND status = 'paid';Mark your progress
Create a practice profile to mark this question.