Why might MySQL not use an index you created?

Summary

Common causes: a function or expression on the column (WHERE DATE(created_at) = ...), implicit type conversion (comparing a VARCHAR column to a number), a leading wildcard (LIKE '%abc'), not using the leftmost prefix of a composite index, OR across different columns, low selectivity, or stale statistics.

Longer Answer

Fixes:

• Rewrite to a range: created_at >= '2026-10-01' AND created_at < '2026-10-02' instead of DATE(created_at) = ...; or index the expression (functional indexes, 8.0.13+).
• Compare with the right type: WHERE phone = '0123', not = 123 (string-to-number conversion disables the index and can match wrong rows).
• Different collations or character sets between joined columns also prevent index use.
• OR across columns: use UNION ALL of two indexed queries, or rely on index merge.
• If the optimiser estimates that a large share of the table matches, a full scan can genuinely be faster.
• Refresh statistics with ANALYZE TABLE; as a last resort use FORCE INDEX (and document why).

Key Points

• Functions on columns, type conversion, leading wildcards.
• Leftmost prefix not used; OR across columns.
• Collation mismatches in joins.
• Low selectivity can make a scan cheaper.