What causes a query to ignore an index?

Most often a function or expression wrapping the filtered column — WHERE YEAR(order_date) = 2024 cannot seek an index on order_date. Rewrite as a half-open range.

Also: a leading wildcard in LIKE, an implicit type cast between the column and the literal, and low selectivity where scanning is genuinely cheaper so the planner correctly declines the index.

With a composite index, remember it serves a left-to-right prefix — an index on (region, product) will not efficiently serve a predicate on product alone.