Why MySQL ignores your index

Before you start

Abstract illustration of a path that bypasses a shape, representing an index that is not used

You created an index and the query is still slow. Run EXPLAIN on it first. If the key column is NULL, or names a different index than you expected, MySQL decided not to use yours. The causes below are the ones that come up most. See Read a MySQL EXPLAIN plan for the columns.

The usual causes

A function on the column

WHERE DATE(created_at) = ‘2025-01-01’ cannot use an index on created_at. Rewrite it as a range: created_at >= ‘2025-01-01’ AND created_at < ‘2025-01-02’.

A type mismatch

Comparing a string column to a number (WHERE phone = 5551234) makes MySQL convert every row, so the index is skipped. Quote the value.

Wrong column order

An index on (customer_id, created_at) does not help a search on created_at alone. The leftmost column must be in the condition.

A leading wildcard

LIKE ’%smith’ cannot use a B-tree index. LIKE ’smith%’ can.

OR across columns

WHERE a = 1 OR b = 2 may need a separate index for each side, or a rewrite as a UNION.

It would not be faster

If the condition matches a large share of the table, reading it all is cheaper than jumping through the index, and the optimizer is right to scan.

Stale statistics

The optimizer chooses from table statistics. After large changes, run ANALYZE TABLE and check again.

Test it, do not force it

FORCE INDEX tells MySQL to use a specific index. It is useful as an experiment: if the forced query is much faster, you know the index is right and the cause is one of the above.

EXPLAIN SELECT id, total
FROM orders FORCE INDEX (idx_orders_customer_created)
WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

Do not leave it in the application. A forced index choice goes on being forced after the data changes and a different index would be better.

Next step

Paste the query and its EXPLAIN output into dbtempo to see which of these causes it finds. To build or drop indexes, see Create, show and drop indexes in MySQL.

Sources