7 Common MySQL Index Pitfalls That Can Kill Query Performance

1. OR conditions can disable indexes

When a query mixes an indexed column with an unindexed column using OR, MySQL may choose a full‑table scan instead of using the index.

EXPLAIN SELECT * FROM t_user WHERE user_id = 123456;

The plan shows index usage on user_id. Adding an OR age = 18 (where age lacks an index) changes the plan to a full scan.

EXPLAIN SELECT * FROM t_user WHERE user_id = 123456 OR age = 18;

Conclusion: If any OR operand lacks an index, MySQL may abandon the index entirely.

2. Leading wildcards in LIKE break indexes

Using a pattern that starts with % forces a full scan because the index cannot be used for prefix matching.

EXPLAIN SELECT * FROM t_user WHERE name = '%leixiaoshuai%';

The plan shows a full‑table scan. Removing the leading % ( LIKE 'leixiaoshuai%') restores index usage.

EXPLAIN SELECT * FROM t_user WHERE name = 'leixiaoshuai%';

Even when selecting only id and name with a leading %, the query can use a covering index and thus employ the index again.

EXPLAIN SELECT id, name FROM t_user WHERE name = '%leixiaoshuai%';

Key takeaways: avoid leading wildcards, or use a covering index to mitigate the impact.

3. Forgetting quotes around string literals disables indexes

Comparing a VARCHAR column to an unquoted numeric literal causes an implicit type conversion, leading MySQL to perform a full scan. EXPLAIN SELECT * FROM t_user WHERE name = 123; The plan shows a full scan despite an index on name. Adding proper quotes ( '123') makes the index work.

EXPLAIN SELECT * FROM t_user WHERE name = '123';

4. Applying built‑in functions to indexed columns disables indexes

Wrapping an indexed column with a function (e.g., UPPER(name)) prevents MySQL from using the index, resulting in a full scan.

EXPLAIN SELECT * FROM t_user WHERE UPPER(name) = 'LEIXIAOSHUAI';

5. IS NULL / IS NOT NULL on indexed columns

Queries that test an indexed column for IS NOT NULL generally still use the index.

EXPLAIN SELECT * FROM t_user WHERE name IS NOT NULL;

The plan confirms index usage. The same holds for address IS NOT NULL.

EXPLAIN SELECT * FROM t_user WHERE address IS NOT NULL;

However, combining OR between two such conditions ( name IS NOT NULL OR address IS NOT NULL) can cause the optimizer to abandon the index.

EXPLAIN SELECT * FROM t_user WHERE name IS NOT NULL OR address IS NOT NULL;

6. Arithmetic on indexed columns disables indexes

Performing arithmetic directly on an indexed column (e.g., user_id + 1 = 456789) prevents index usage.

EXPLAIN SELECT * FROM t_user WHERE user_id + 1 = 456789;

7. Composite index order matters (the left‑most principle)

A composite index on (name, age) can be used when the query filters on name alone, on both name and age, or on the leftmost prefix. It cannot be used when filtering only on age.

EXPLAIN SELECT * FROM t_user WHERE name = 'leixiaoshuai' AND age = 18;

Uses the composite index.

EXPLAIN SELECT * FROM t_user WHERE name = 'leixiaoshuai';

Also uses the composite index because name is the leftmost column. EXPLAIN SELECT * FROM t_user WHERE age = 18; Results in a full scan because the leftmost column ( name) is missing.

Key principle: a composite index can be used only for the leftmost prefix of its column list (the “left‑most matching” rule).

Conclusion

The seven scenarios above—OR conditions, leading wildcards, missing quotes, functions on indexed columns, NULL checks, arithmetic on indexed columns, and improper composite index ordering—are the most common ways indexes can unexpectedly fail, leading to severe performance degradation. Understanding MySQL’s optimizer behavior and adhering to these guidelines helps keep queries fast and avoids costly full‑table scans.