Why might the database ignore an index on a column?
Common reasons are wrapping the column in a function, a leading wildcard in LIKE, a type mismatch, a condition that matches most rows, or outdated statistics. The optimizer then prefers a full scan.
Explain it simply
A book index sorted by surname cannot help you find everyone whose surname ends in "kar", and if you want 90 percent of the pages anyway, flipping through the index is slower than just reading the book.
Example
Assume an index on email and one on created_at:
-- index is skipped, function applied to the column
SELECT * FROM users WHERE LOWER(email) = 'asha@example.com';
-- index is skipped, leading wildcard
SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- index is usable, range on a bare column
SELECT * FROM users
WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01';Other causes:
- Implicit type conversion, such as comparing a text column to a number.
- Low selectivity: if 95 percent of rows have
status = 'active', a scan is cheaper than 95 percent random lookups. - Stale statistics after a big data load.
- The query uses columns not at the start of a composite index.
Fixes are to keep the column bare, create a function-based index, or rewrite the filter as a range. Always confirm with EXPLAIN.
What to say in the interview
"Indexes are skipped when the predicate is not sargable, for example a function on the column or a leading wildcard, or when the optimizer judges a scan cheaper. I check the plan with EXPLAIN and rewrite the predicate."
Common mistakes
- Assuming that creating an index guarantees its use.