7 Common MySQL Index Pitfalls and How to Avoid Them

A recent production issue revealed a slow SQL caused by an index that became ineffective in certain situations, leading to full table scans and blocked queries.

Leader: Reflect on why such a basic mistake happened. Me: I’ll write a summary tonight and hope not to lose my performance bonus. Leader: ... see if your article gets over 100 likes. Me: ... unlikely, they prefer freebies.

Below is a summary of common index failure scenarios to help you earn a good year‑end bonus.

We first create a sample table with a primary key, a unique index on user_id, and ordinary indexes on name and address:

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_user_id` (`user_id`) USING BTREE,
  KEY `index_name` (`name`) USING BTREE,
  KEY `index_address` (`address`) USING BTREE
) ENGINE=InnoDB;

OR condition may cause index loss

Simple query using the indexed user_id column:

EXPLAIN SELECT * FROM t_user WHERE user_id = 123456;

The execution plan shows the index is used.

Adding an OR condition on a non‑indexed age column:

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

The plan now shows a full table scan, meaning the index is ineffective.

Analysis:

If an OR includes a column without an index, MySQL may need to scan the whole table.

If all OR columns are indexed, the optimizer might still choose a full scan depending on cost.

The optimizer can deem the index ineffective, which is reasonable.

Note: Indexes may still be used when every column in the OR has an index; try it yourself.

LIKE wildcard can invalidate index

Using a leading wildcard:

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

The plan shows a full table scan, so the index is lost.

Removing the leading % restores index usage:

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

Selecting only id and name with the leading wildcard:

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

Surprisingly, the index is used again because of a covering index.

Analysis: A leading % disables the index; using a covering index or moving the wildcard to the end can mitigate the issue.

Key term: Covering Index (an index that contains all columns needed by the query).

Missing quotes on string columns can cause index loss

Querying a VARCHAR column without quotes: EXPLAIN SELECT * FROM t_user WHERE name = 123; The plan shows a full table scan despite the index.

Adding proper quotes restores index usage:

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

Analysis: Without quotes MySQL performs an implicit type conversion to a number, preventing the index from being used.

Applying functions to indexed columns can invalidate index

Using UPPER() on an indexed name column:

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

The optimizer cannot use the index and performs a full scan.

IS NULL / IS NOT NULL on indexed columns may affect index usage

Checking name IS NOT NULL and address IS NOT NULL both use the index as expected:

EXPLAIN SELECT * FROM t_user WHERE name IS NOT NULL;

EXPLAIN SELECT * FROM t_user WHERE address IS NOT NULL;

Combining them with OR leads to a full scan, indicating index loss:

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

Arithmetic on indexed columns disables index

Adding +1 to an indexed user_id column:

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

Composite index order matters (left‑most principle)

We create a table with a composite index on (name, age):

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name_age` (`name`,`age`) USING BTREE
) ENGINE=InnoDB;

Querying both name and age uses the composite index:

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

Querying only name also uses the composite index (left‑most column):

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

Querying only age (the second column) does **not** use the index, violating the left‑most rule:

EXPLAIN SELECT * FROM t_user WHERE age = 18;

Analysis:

The composite index creates multiple left‑most prefixes: (name), (name, age). This is the “left‑most matching principle”.

If a query does not follow the left‑most order, the index may be ignored, depending on the optimizer.

Summary

The article lists seven typical situations where MySQL indexes become ineffective: OR conditions, leading wildcards in LIKE, missing quotes on string literals, applying functions, NULL checks combined with OR, arithmetic on indexed columns, and misuse of composite index order. Understanding these cases helps prevent performance degradation.