An index is unused if the query wraps the column in a function

An index holds the column’s values, in order. YEAR(created_at) is not one of those values, so there is nothing for the optimiser to look up — it has to compute the expression for every row, which means reading every row. The index is still there, still maintained on every write, and completely unused.

-- index on created_at. type: ALL, key: NULL
SELECT COUNT(*) FROM orders WHERE YEAR(created_at) = 2012;

-- same rows, a range scan on the same index
SELECT COUNT(*) FROM orders
 WHERE created_at >= '2012-01-01'
   AND created_at <  '2013-01-01';

-- the same mistake wearing different clothes
WHERE DATE(created_at) = '2012-11-20'
WHERE LOWER(email) = '[email protected]'
WHERE CONCAT(first_name, ' ', last_name) LIKE 'Ada%'
WHERE sku = 4900          -- sku is VARCHAR: MySQL casts the column, not the value

The rewrite always has the same shape — leave the column bare on one side and move the arithmetic to the other, turning a truncation into a range. Two of those variants deserve a note of their own. LOWER() is usually pure loss in MySQL, because the default collations are already case-insensitive and WHERE email = '[email protected]' matches without it. The last one is the case that hides in plain sight: comparing a VARCHAR column to a numeric literal makes MySQL cast the column rather than the literal, so every row is converted and the index is dead, with nothing in the SQL that looks like a function call. Quoting the value fixes it. EXPLAIN confirms all of these the same way — type: ALL with key: NULL — which makes this the cheapest class of slow query there is to find, once you know the shape to look for.