MySQL

MySQL Slow Query Despite Having an Index

Having an index on a column doesn't guarantee MySQL will actually use it efficiently, or that using it will make a query fast — the query optimizer makes cost-based decisions that don't always match what a developer expects just from knowing an index exists.

The Problem

You add an index on a column used in a WHERE clause, expecting a clear speedup, but the query remains just as slow:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

SELECT * FROM orders WHERE customer_id = 12345;
-- still slow, as if no index exists

Running SHOW INDEX FROM orders; confirms the index exists and is defined correctly, which makes the continued slowness confusing.

Why It Happens

MySQL's query optimizer decides whether to use an index based on cost estimation, not simply whether an index exists on the relevant column. Common reasons a query ignores an available index or stays slow anyway:

  • Table statistics are outdated, causing the optimizer's estimate of how many rows match to be wrong, leading it to choose a full table scan over the index even when the index would actually be faster
  • The query returns a large enough percentage of the table that a full scan genuinely is faster than jumping between index and table data repeatedly — for filters matching a large portion of rows, this is often the correct choice, not a bug
  • A function or implicit type conversion is applied to the indexed column in the query (for example, comparing an integer column against a string value), which prevents MySQL from using a standard index efficiently
  • The index exists but doesn't cover all the columns needed for the query, forcing MySQL to still access the full table row for additional columns even after using the index to find matching rows

The Fix

Start by seeing what the optimizer is actually doing, rather than assuming:

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;

Check the type column in the output. A value of ALL means a full table scan, which confirms the index isn't being used at all. A value like ref or range means the index is being used — in that case the query may be slow for a different reason entirely (like returning too many matching rows, or a slow join afterward), not because the index is being skipped.

If statistics are stale, refresh them and re-run the query:

ANALYZE TABLE orders;

This alone resolves a surprising number of "index exists but isn't used" cases, especially after large bulk inserts, updates, or deletes that weren't followed by MySQL automatically updating its internal statistics.

If a type mismatch is the issue (a very common, easy-to-miss cause), make sure you're comparing the column against a value of the exact same type it was defined with:

-- If customer_id is INT, avoid comparing it as a string
SELECT * FROM orders WHERE customer_id = 12345;   -- correct
SELECT * FROM orders WHERE customer_id = '12345'; -- can prevent index usage

If you're filtering on multiple columns together, consider whether a composite index covering all of them, in the right column order, would serve the query better than a single-column index — MySQL can only use a composite index efficiently for the leftmost columns present in the query's conditions:

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

For queries that only need a few columns, a covering index (one that includes every column the query needs) lets MySQL satisfy the entire query from the index alone, without touching the full table row at all:

CREATE INDEX idx_orders_covering ON orders(customer_id, status, created_at);

Still Not Working?

If EXPLAIN confirms the index is being used (ref or range, not ALL) but the query is still slow, the bottleneck is likely elsewhere — a subsequent join, sorting a large result set (ORDER BY without a supporting index), or simply a genuinely large number of matching rows requiring real I/O regardless of indexing. Compare the rows estimate in the EXPLAIN output against the actual row count returned by the query; a large gap between the two often points back to outdated statistics steering the optimizer's decisions, even when the index itself is technically being used correctly.

You might also like