Why MySQL Indexes Fail and 14 Proven Fixes for Faster Queries

Introduction

MySQL index not being used (often called “index failure”) is one of the most frequent performance problems in database development and optimization, causing query latency to drop from milliseconds to seconds.

The article starts from the principle of how indexes work, then deeply analyzes eight classic causes and six advanced traps, finally offering production‑grade optimization advice.

1. Core Concept: How Indexes Work

Think of an index as a book's table of contents:

No table of contents: the engine must scan every page (full table scan).

With a table of contents: the engine can jump directly to the needed page (row location).

Index failure: the optimizer believes scanning the whole table is faster than using the index, so it discards the index.

2. Eight Common Reasons for Index Failure

1. Using Functions or Expressions on Indexed Columns

-- Fails: applying a function to the indexed column
SELECT * FROM users WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2023-10-01';

-- Works: apply the function to a constant instead
SELECT * FROM users WHERE create_time >= '2023-10-01 00:00:00' AND create_time < '2023-10-02 00:00:00';

2. Implicit Type Conversion

-- Fails: user_id is VARCHAR but the query passes a number
SELECT * FROM users WHERE user_id = 123;

-- Works: keep the type consistent
SELECT * FROM users WHERE user_id = '123';

3. LIKE with a Leading Wildcard

-- Fails: leading % prevents index usage
SELECT * FROM articles WHERE content LIKE '%MySQL%';

-- Works: avoid leading %
SELECT * FROM articles WHERE content LIKE 'MySQL%';

-- Recommendation: use FULLTEXT or Elasticsearch for full‑text search.

4. Violating the Left‑most Prefix Rule (Composite Index)

-- Composite index (name, city, age)
SELECT * FROM persons WHERE name = 'John';                     -- ✅ Uses index
SELECT * FROM persons WHERE name = 'John' AND city = 'NY';   -- ✅ Uses index
SELECT * FROM persons WHERE city = 'NY';                     -- ❌ Index ignored

5. Using NOT / != / NOT IN on Indexed Columns

-- May cause index to be ignored
SELECT * FROM products WHERE status != 'deleted';
SELECT * FROM orders WHERE user_id NOT IN (1,2,3);

6. OR Connecting Different Columns

-- Optimizer may not apply Index Merge
SELECT * FROM users WHERE name = 'Alice' OR email = '[email protected]';

-- Better: rewrite as UNION ALL
SELECT * FROM users WHERE name = 'Alice'
UNION ALL
SELECT * FROM users WHERE email = '[email protected]';

7. Data Distribution Causing Optimizer to Drop Index

-- When 90% of rows have status='pending', the index is often ignored
SELECT * FROM orders WHERE status = 'pending';

8. Index‑Specific Problems

Stale statistics: run ANALYZE TABLE table_name; to refresh.

Corrupted index: use CHECK TABLE / REPAIR TABLE .

3. Advanced Traps and Practical Optimizations

9. ORDER BY and Index Interaction

When ORDER BY columns match the WHERE columns, the index can avoid a Using filesort operation.

-- Fails: index cannot cover sorting on age+name
SELECT * FROM users ORDER BY age, name;

10. Range Queries Truncate Composite Indexes

In a composite index (a, b, c), a range condition on b stops the optimizer from using c.

SELECT * FROM t WHERE a = 1 AND b > 10 AND c = 5;  -- index on c is ignored

11. Subqueries and Indexes

IN (subquery)

may lead to a full scan if not optimized.

Best practice: rewrite as a JOIN and ensure join columns are indexed.

12. NULL‑Related Pitfalls

Conditions using IS NULL or IS NOT NULL sometimes bypass indexes.

Recommendation: avoid NULLs; use default values instead.

13. Covering Composite Indexes

If the index contains all columns referenced by the query, the engine can satisfy the query directly from the index (covering index), eliminating the need to read the table rows.

-- Composite index (name, city, age)
SELECT name, city FROM persons WHERE name = 'John';  -- ✅ Uses index only

14. Index Monitoring and Tuning Tools

Slow query log ( slow_query_log)

pt‑query‑digest (Percona Toolkit)

Performance Schema / sys schema

4. How to Diagnose Whether an Index Is Used

Run EXPLAIN or EXPLAIN FORMAT=JSON and examine the key fields:

type : access type (best to worst: const → ref → range → index → ALL).

key : the actual index used; NULL means no index.

rows : estimated rows examined.

Extra : look for Using filesort, Using temporary, etc.

EXPLAIN SELECT * FROM users WHERE name = 'John';

5. Summary and Best Practices

Always run EXPLAIN on SQL statements, just like debugging code.

Composite index design rules: high‑cardinality columns on the left, obey the left‑most prefix, and try to cover query columns.

Avoid functions, implicit conversions, and range queries that precede other columns on indexed fields.

NULL, OR, and NOT are high‑risk operations for index loss.

Regularly refresh statistics, monitor slow queries, and use the mentioned tools for continuous optimization.