10 Common MySQL Index Failure Scenarios and How to Fix Them

1. How Indexes Work

MySQL InnoDB uses B+‑tree indexes, which store index keys in sorted order in leaf nodes, allowing the optimizer to perform binary search. If a query condition breaks this ordering or matching ability, the optimizer discards the index and falls back to a full‑table scan.

2. Ten Typical Index‑Failure Scenarios

Scenario 1 – Indexed Column Used in a Function or Expression

Problem: The WHERE clause applies a function or arithmetic operation to an indexed column.

Why it fails: The index stores the raw column values; applying a function changes the value, destroying the order that the B+‑tree relies on.

Wrong example:

-- Using a date function
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- Arithmetic operation
SELECT * FROM products WHERE price * 0.8 > 100;

Correct rewrite:

-- Convert to a range query
SELECT * FROM users
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

-- Move the calculation to a constant
SELECT * FROM products
WHERE price > 125;

Performance impact: On a table with 1 000 000 rows, the wrong query takes ~1200 ms, while the corrected version finishes in ~15 ms (≈80× faster).

Scenario 2 – Implicit Type Conversion

Problem: The data type of the query constant does not match the indexed column’s type, causing MySQL to cast the column.

Why it fails: Implicit CAST acts like a function on the indexed column, breaking the index order.

Wrong example:

-- phone is VARCHAR but compared with a number
SELECT * FROM users WHERE phone = 13800138000;

-- created_at is DATETIME but compared with a number
SELECT * FROM orders WHERE created_at = 20240101;

Correct rewrite:

SELECT * FROM users WHERE phone = '13800138000';
SELECT * FROM orders WHERE created_at = '2024-01-01';

Technical detail: MySQL converts the string to a number, effectively executing CAST(phone AS SIGNED) = 13800138000, which prevents index usage.

Scenario 3 – LIKE with a Leading Wildcard

Problem: Queries use LIKE '%keyword%' or LIKE '%keyword'.

Why it fails: B+‑tree indexes are ordered by prefix; a leading wildcard prevents the optimizer from locating a start point, forcing a full scan.

Wrong example:

SELECT * FROM users WHERE username LIKE '%admin%';
SELECT * FROM products WHERE name LIKE '%手机';

Correct rewrite:

-- Use a suffix wildcard
SELECT * FROM users WHERE username LIKE 'admin%';

-- Or use a full‑text index
ALTER TABLE products ADD FULLTEXT INDEX idx_name(name);
SELECT * FROM products WHERE MATCH(name) AGAINST('手机');

Performance comparison: On a 5 000 000‑row product table, the wildcard query takes ~8 s, while the full‑text index returns results in ~120 ms.

Scenario 4 – Violating the Left‑most Prefix Rule

Problem: A composite index is defined, but the query does not include the leftmost column.

Why it fails: The index is built in column order; skipping the first column loses the ability to locate the start position.

Wrong example:

SELECT * FROM employees
WHERE position = '工程师' AND salary > 10000;

Correct rewrite:

-- Include the leftmost column
SELECT * FROM employees
WHERE department = '技术部' AND position = '工程师' AND salary > 10000;

-- Or create a new index that matches the query
CREATE INDEX idx_position_salary ON employees(position, salary);

Case note: In an e‑commerce order table with index (user_id, status, create_time), a query that filters only by status will not use the index; the user_id condition must be present.

Scenario 5 – OR Connecting a Non‑Indexed Column

Problem: An OR condition mixes indexed and non‑indexed columns.

Why it fails: MySQL must guarantee correct results for all rows, so if any part of the OR cannot use an index, it chooses a full scan.

Wrong example:

SELECT * FROM users WHERE age = 25 OR address = '北京市';

Correct rewrite:

-- Add an index on address
CREATE INDEX idx_address ON users(address);

-- Or split the query with UNION ALL
SELECT * FROM users WHERE age = 25
UNION ALL
SELECT * FROM users WHERE address = '北京市' AND age <> 25;

Optimizer note: MySQL 5.7+ can apply Index Merge, but it is usually slower than a single composite index.

Scenario 6 – Using NOT EQUAL or NOT IN

Problem: Queries filter with !=, <> or NOT IN on an indexed column.

Why it fails: B+‑tree indexes excel at equality and range scans; a NOT‑EQUAL predicate often requires scanning most rows.

Wrong example:

SELECT * FROM orders WHERE status != 'completed';
SELECT * FROM products WHERE id NOT IN (1,2,3);

Correct rewrite:

-- List the values you actually need
SELECT * FROM orders WHERE status IN ('pending','shipped','cancelled');

-- Use a range query instead of NOT IN
SELECT * FROM products WHERE id < 1 OR id > 3;

Exception: When the excluded set is tiny, the index may still be used; verify with EXPLAIN.

Scenario 7 – Range Query Blocking Subsequent Index Columns

Problem: In a composite index, a range condition (>, <, BETWEEN) appears before other columns.

Why it fails: After a range predicate, the optimizer cannot use the ordering of later columns for precise matches.

Wrong example:

SELECT * FROM orders
WHERE status = 'paid'
  AND create_time > '2024-01-01'
  AND amount = 1000;  -- amount column becomes unusable

Correct rewrite:

-- Reorder the index so that the range column is last
CREATE INDEX idx_status_amount_time ON orders(status, amount, create_time);
-- Or use a covering index that includes the needed columns
CREATE INDEX idx_status_time_amount ON orders(status, create_time, amount, order_id);
SELECT order_id, amount FROM orders
WHERE status = 'paid' AND create_time > '2024-01-01' AND amount = 1000;

Design principle: Place equality columns on the left side of a composite index and range columns on the right.

Scenario 8 – IS NULL / IS NOT NULL Queries

Problem: Filtering with IS NULL or IS NOT NULL on an indexed column.

Why it fails: NULL handling is special; IS NOT NULL may require scanning all non‑null rows, leading the optimizer to prefer a full scan.

Wrong example:

SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM products WHERE description IS NOT NULL;

Optimization:

-- Avoid NULLs by defining a default value
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL DEFAULT '';

-- Use a covering index if needed
CREATE INDEX idx_desc_covering ON products(description, id, name);
SELECT id, name FROM products WHERE description IS NOT NULL;

Statistics note: The proportion of NULLs in the index influences the optimizer’s choice.

Scenario 9 – Low Selectivity Index

Problem: The indexed column has very few distinct values (e.g., gender, status).

Why it fails: When selectivity falls below ~30 %, the optimizer may deem a full scan cheaper than using the index.

Diagnostic query:

SELECT COUNT(DISTINCT gender) / COUNT(*) AS selectivity FROM users;

Optimization options:

-- Create a composite index to raise selectivity
CREATE INDEX idx_gender_age ON users(gender, age);

-- Or force index usage (use with caution)
SELECT * FROM users FORCE INDEX(idx_gender) WHERE gender = 'F';

Design advice: Avoid single‑column indexes on low‑cardinality fields; combine them with higher‑cardinality columns.

Scenario 10 – Table Too Small

Problem: The table contains very few rows (often < 100); even with an index, MySQL may choose a full scan.

Why it fails: The overhead of reading the index and then the table outweighs the benefit for tiny tables.

Example:

SELECT * FROM config WHERE gender = 'male';

Handling strategy:

Do not create indexes on tiny tables.

Periodically run ANALYZE TABLE table_name; to keep statistics up‑to‑date.

Monitor execution plans as data grows.

Optimizer logic: Cost‑based estimation may favor a full scan for small tables.

3. Diagnosis Tools and Methods

3.1 Deep EXPLAIN Analysis

EXPLAIN SELECT * FROM users WHERE age = 25;

Key fields to read:

type : ALL means full scan; ref / range indicate index usage.

key : the actual index used; NULL means the index was ignored.

rows : estimated number of rows examined.

Extra : Using index denotes a covering index; Using filesort signals a required sort operation.

3.2 Slow‑Query Log Monitoring

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- record queries > 1 s
SET GLOBAL log_queries_not_using_indexes = 'ON';

3.3 Index Usage Statistics

SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'your_database';

ANALYZE TABLE table_name;

4. Best‑Practice Summary

Keep indexed columns “pure” : avoid functions, arithmetic, or implicit casts on them.

Follow the left‑most prefix rule when designing composite indexes.

Use covering indexes wisely to eliminate back‑table lookups.

Maintain up‑to‑date statistics so the optimizer makes accurate decisions.

Monitor index usage and drop unused indexes to reduce maintenance overhead.

Test changes in a staging environment and verify execution plans before production rollout.

Index optimization is an ongoing process that must consider business scenarios, data distribution, and query patterns. Mastering these failure scenarios enables developers to avoid performance traps during development and helps operations teams quickly pinpoint root causes in production.