When MySQL Indexes Fail: 4 Common Pitfalls to Avoid

MySQL will not use an index in several situations.

1. Composite index with non‑leading column

If a composite index (a,b) is defined but the query condition uses column b instead of the first column a, the index is ignored. Using a as the condition will employ the index.

2. LIKE queries with a leading wildcard

A LIKE pattern that starts with '%' (e.g., WHERE a LIKE '%xxx') prevents index usage, while a trailing wildcard ( WHERE a LIKE 'xxx%') can still use the index. For extensive fuzzy searches, consider full‑text search engines such as Sphinx that support Chinese.

3. OR conditions containing non‑indexed columns

When an OR clause mixes an indexed column with a non‑indexed one (e.g., WHERE a='x' OR b='y' where b is not indexed), MySQL cannot use the index.

4. String literals without quotes

If a string‑type indexed column is compared without surrounding single quotes ( WHERE a=111), the index is not used. Using quotes ( WHERE a='111') enables the index. Unquoted strings are interpreted as column names, leading to errors.