Why Your MySQL Index Isn’t Used and How to Fix It

Verify Index Usage

Before troubleshooting, confirm whether MySQL actually uses an index by running EXPLAIN on the query. The output shows possible_keys (indexes considered) and key (index chosen). Example:

EXPLAIN SELECT * FROM people WHERE first_name = 'Aaron';

Common Reasons Index Is Ignored

Reason 1: A Better Index Exists

When multiple indexes can satisfy a query, the optimizer picks the most selective one. For a query filtering on first_name and state, the optimizer may choose first_name if its selectivity is higher.

Reason 2: Low Selectivity and Cardinality

Indexes with few distinct values (low selectivity) are less useful. Compute cardinality and selectivity with:

SELECT COUNT(DISTINCT first_name) AS first_name_cardinality,
       COUNT(DISTINCT state) AS state_cardinality,
       COUNT(DISTINCT first_name) / COUNT(*) AS first_name_selectivity,
       COUNT(DISTINCT state) / COUNT(*) AS state_selectivity
FROM people;

High selectivity (e.g., first_name) makes the index effective, while low selectivity (e.g., state) does not.

Reason 3: Selectivity Varies by Query

A column’s overall selectivity may be low, but for a specific predicate it can be high. For example, if 99% of rows have type='user' and 1% have type='admin', a query filtering on type='admin' can still benefit from an index.

Reason 4: Stale Statistics

Out‑of‑date statistics can mislead the optimizer. Refresh them with: ANALYZE TABLE people; Adjust MySQL sampling parameters if needed.

Reason 5: Full Table Scan Is Faster

For small tables or queries that need most rows, a full scan can be cheaper than using an index. In such cases the optimizer may deliberately avoid the index.

Reason 6: Structural Limitations of Indexes

Indexes only support left‑most prefix matches. LIKE 'Aa%' can use an index, but LIKE '%ron' cannot. Composite indexes obey the left‑prefix rule; a query must reference the leading columns in order. Mismatched column types or character sets (e.g., VARCHAR(10) vs CHAR(15)) also prevent index usage.

Reason 7: Functions or Expressions Hide Indexes

Applying functions to indexed columns disables index use, e.g., SELECT * FROM people WHERE YEAR(created_at) = 2023;. Rewrite as a range query to enable the index:

SELECT * FROM people WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';

Reason 8: Invisible Indexes

MySQL can mark an index as INVISIBLE, causing the optimizer to ignore it. Example:

ALTER TABLE people ALTER INDEX first_name INVISIBLE;

Use this for testing the impact of removing an index.

Force an Index

If the optimizer makes a poor choice, you can hint it with USE INDEX(index_name), e.g.:

EXPLAIN SELECT * FROM people USE INDEX (state) WHERE first_name = 'Aaron' AND state = 'TX';

Use such hints cautiously, as they may become suboptimal as data grows.

Conclusion

Understanding how the optimizer selects indexes, data distribution, and index structure helps you diagnose why an index is not used and apply appropriate fixes to improve query performance.