Why does WHERE YEAR(created_at) = 2026 not use the index?

Symptom: the query does a full table scan although created_at is indexed.

Cause: a B-tree index is sorted by the stored value of created_at. When the condition wraps the column in a function, the database would have to compute YEAR() for every row to compare, so it cannot seek into the index. The predicate is not sargable (search-argument-able).

Fix: compare the bare column against a range, and keep the function out of the column side.

-- slow: function on the column
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- fast: range on the raw column
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

The same trap appears with LOWER(email) = '[email protected]', price * 2 > 100 (write price > 50), an implicit type cast on the column, and LIKE '%abc' with a leading wildcard.

When you cannot rewrite the query, add an expression index (PostgreSQL) or an indexed generated column (MySQL) that stores the computed value.

Rule of thumb: keep the indexed column alone on one side of the comparison.