7 Common MySQL Index Failure Scenarios and How to Fix Them

1. Composite Index Not Using Leftmost Prefix

When a composite index (a,b,c) is defined, the query must start with the leftmost column a to use the index.

Failure example:

SELECT * FROM table WHERE b = 1 AND c = 2; -- ❌ Index not used

Correct usage:

WHERE a = ? -- ✅
WHERE a = ? AND b = ? -- ✅
WHERE a = ? AND b = ? AND c = ? -- ✅
-- MySQL optimizer reorders = conditions to match index order, so:
WHERE b = ? AND a = ? AND c = ? -- ✅ Also uses index

2. Using Functions or Operations on Indexed Columns

Applying a function (e.g., YEAR()) or arithmetic operation on an indexed column prevents index usage because MySQL must compute the function for every row.

Failure example:

SELECT * FROM orders WHERE YEAR(create_time) = 2025; -- ❌ Index not used

Correct rewrite using range query:

-- Convert to range query
SELECT * FROM orders 
WHERE create_time BETWEEN '2025-01-01' AND '2025-12-31'; -- ✅

3. Implicit Type Conversion

When the column type and the query value type differ, MySQL performs implicit conversion on the column side, which prevents index usage.

Failure example: user_id is VARCHAR, but the query passes a number.

-- user_id is VARCHAR type
SELECT * FROM users WHERE user_id = 1001; -- ❌ Index not used (column converted to number)

Correct usage: Keep types consistent.

SELECT * FROM users WHERE user_id = '1001'; -- ✅ Type matches

4. LIKE Query with Leading Wildcard

A LIKE pattern starting with % cannot use a B-tree index because the index is ordered left-to-right.

Failure example:

SELECT * FROM users WHERE name LIKE '%王'; -- ❌ Index not used

Correct usage: Place wildcard at the end.

SELECT * FROM users WHERE name LIKE '王%'; -- ✅ Can use index

5. OR Connecting a Non-Indexed Column

If one side of an OR condition has no index, MySQL often chooses a full table scan.

Failure example: age has an index, address does not.

-- age has index, address has no index
SELECT * FROM users WHERE age > 25 OR address = '北京'; -- ❌ Index not used

Correct rewrite using UNION:

-- Split into UNION
SELECT * FROM users WHERE age > 25
UNION
SELECT * FROM users WHERE address = '北京'; -- ✅

6. Using IS NULL / IS NOT NULL

IS NULL

can typically use an index; IS NOT NULL usually cannot.

SELECT * FROM users WHERE name IS NULL; -- ✅ Usually uses index
SELECT * FROM users WHERE name IS NOT NULL; -- ❌ Index not necessarily used, usually not

7. NOT IN / NOT EXISTS

Negative subqueries like NOT IN or NOT EXISTS often prevent index usage.

Failure example:

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist); -- ❌ Index not used

Correct rewrite using LEFT JOIN:

-- Rewrite with LEFT JOIN
SELECT u.* FROM users u
LEFT JOIN blacklist b ON u.id = b.user_id
WHERE b.user_id IS NULL; -- ✅

Additional Notes

Even with correct syntax, the optimizer may choose a full table scan when the table is small because it estimates that scanning is faster. Other causes of index failure include duplicate indexes, stale index statistics, and range queries breaking composite index usage. Analyze each case with EXPLAIN to confirm the execution plan.