6 Common MySQL Index Pitfalls That Kill Query Performance

Index Storage Structures

MySQL's storage engine determines the index data structure. InnoDB uses a B+Tree where leaf nodes store the actual row data (clustered index), while MyISAM also uses a B+Tree but its leaf nodes store physical pointers to the data. Primary keys in InnoDB are clustered; other indexes are secondary.

Example diagrams illustrate the difference:

How MySQL Uses Indexes

When a query filters on the primary‑key column, MySQL can locate the row directly in the clustered index. For a secondary index, MySQL first searches the secondary B+Tree to obtain the primary‑key value, then looks up the row in the clustered index – a process called back‑table (回表). If the secondary index contains all columns needed by the query, the engine can satisfy the query from the index alone, known as a covering index .

// id field is primary key
select * from t_user where id=1;

For a secondary index on name:

// back‑table example
select * from t_user where name="林某";

When the index covers the needed columns:

// covering index example
select id from t_user where name="林某";

Six Scenarios That Cause Index Failure

Left‑most or both‑sides wildcard LIKE : Queries like WHERE name LIKE '%林' or WHERE name LIKE '%林%' cannot use the index, resulting in type=ALL (full scan). Prefix wildcards such as WHERE name LIKE '林%' do use the index ( type=range).

Applying functions to indexed columns : Using a function on an indexed column, e.g., WHERE LENGTH(name)=6, forces a full scan because the index stores raw values, not function results.

Expression calculations on indexed columns : Conditions like WHERE id+1=10 prevent index usage. Rewriting as WHERE id=10-1 restores index usage.

Implicit type conversion : Comparing a string indexed column with a numeric literal (e.g., WHERE phone=1300000001 where phone is VARCHAR) makes MySQL cast the column, effectively applying a function and breaking the index. Casting the literal instead ( WHERE CAST(phone AS SIGNED)=1300000001) keeps the index.

Composite (union) index left‑most rule : A composite index (a,b,c) can be used only if the query predicates start with the leftmost column(s). Queries that omit a or start with b or c alone cause the index to be ignored.

OR conditions mixing indexed and non‑indexed columns : In WHERE id=1 OR age=18, because age is not indexed, MySQL performs a full scan. Adding an index on age enables an index_merge strategy, scanning both indexes and merging results.

Each scenario is demonstrated with sample SQL statements and EXPLAIN output showing type=ALL for full scans versus type=range, type=index_merge, or Extra=Using index condition when the index is effective.

Conclusion

Understanding the underlying index storage, the distinction between clustered and secondary indexes, and the six common patterns that invalidate indexes enables developers to write queries that fully exploit MySQL indexes and avoid costly full‑table scans.