10 Common MySQL Index Pitfalls and How to Fix Them

Background

Recently a slow SQL query was discovered in production because it used OR and !=, which caused index loss. The article summarizes ten common index‑failure scenarios and offers guidance.

1. OR Conditions May Invalidate an Index

Table user with a normal index on userId:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When the query contains OR and a column without an index ( age), the optimizer falls back to a full table scan, as shown in the screenshots.

Analysis & Conclusion: With OR + an unindexed column, MySQL may first use the userId index, then perform a full scan for age, resulting in a three‑step process (full scan + index scan + merge). If a full scan is chosen from the start, it is faster. The optimizer disables the index for efficiency.

Note: If every column in the OR clause has an index, the index can still be used.

2. String Columns Must Be Quoted in WHERE

Table user with userId defined as varchar(32):

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

If the query passes a numeric literal without quotes, MySQL performs an implicit type conversion to a floating‑point number, causing the index to be ignored. Adding quotes (treating the value as a string) makes the index work.

3. LIKE Wildcards Can Break an Index

Using LIKE '%pattern' forces a full scan because the leading wildcard prevents the B‑tree from being used. Placing the wildcard at the end ( pattern%') or using a covering index allows the index to be used.

Conclusion: LIKE with a leading % disables the index; solutions are to use a covering index or move the wildcard to the trailing position.

4. Composite Indexes Follow the Left‑most Rule

Table with a composite index idx_userid_age(userId, age):

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `age` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_age` (`userId`,`age`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

If the query filters on age only, the index is not used because the leftmost column ( userId) is missing. When the query respects the leftmost order, the index works as expected.

5. Built‑in Functions on Indexed Columns Disable the Index

Example: applying DATE_ADD() to an indexed loginTime column forces a full scan.

6. Arithmetic Operations on Indexed Columns Invalidate the Index

Performing age + 1 or similar calculations on an indexed column prevents MySQL from using the index.

7. Inequality Operators ( != , <> , NOT IN ) May Cause Index Loss

Using these operators on indexed columns leads the optimizer to ignore the index.

8. IS NULL / IS NOT NULL Can Disable an Index When Combined with OR

Individual IS NOT NULL checks on indexed columns use the index, but combining them with OR makes the index ineffective.

9. Join Queries with Mismatched Character Sets May Lose Indexes

If the joined columns have different character sets (e.g., utf8mb4 vs utf8), the optimizer may choose a full scan despite existing indexes.

10. Optimizer May Prefer a Full Scan When It Estimates Lower Cost

If the estimated rows returned exceed about 30 % of the table, MySQL may decide that a full table scan is cheaper than using an index. Low‑cardinality columns (e.g., gender flags) should not be indexed.

Summary and Solution for the Original Slow SQL

The problematic statement used OR together with != on a composite primary key ( user_id, device_id), causing the index to be ignored. The recommended fix is to split the statement into two separate updates and add a regular index on device_id.

By referring to these ten index‑failure patterns, checking the execution plan with EXPLAIN, and adapting queries to the specific scenario, developers can avoid unnecessary full scans and improve database performance.