10 Common MySQL Index Pitfalls and How to Avoid Them

Introduction

The author continues the series on MySQL performance, focusing on ten situations that cause indexes to become ineffective. By creating a sample user table and running concrete queries, the article demonstrates each pitfall with EXPLAIN output and screenshots.

Setup

1. Create the user table

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int DEFAULT '0',
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `height` int DEFAULT '0',
  `address` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`),
  KEY `idx_height` (`height`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2. Insert sample data

INSERT INTO user (id, code, age, name, height, address) VALUES
(1, '101', 21, '周星驰', 175, '香港'),
(2, '102', 18, '周杰伦', 173, '台湾'),
(3, '103', 23, '苏三', 174, '成都');

3. Check MySQL version SELECT VERSION(); Result: 8.0.21 4. View execution plan

EXPLAIN SELECT * FROM user WHERE id=1;

1. Not Satisfying the Left‑most Prefix Rule

When a composite index idx_code_age_name is defined on (code, age, name), queries must start with the leftmost column. The following examples use EXPLAIN to show which queries use the index:

EXPLAIN SELECT * FROM user WHERE code='101';

EXPLAIN SELECT * FROM user WHERE code='101' AND age=21;

EXPLAIN SELECT * FROM user WHERE code='101' AND age=21 AND name='周星驰';

All three use the index because the leftmost column code is present. A query that skips code (e.g., WHERE age=21) fails to use the index.

2. Using SELECT *

When SELECT * retrieves all columns, MySQL performs a full‑table scan, ignoring any index. Changing the query to request only indexed columns (e.g., SELECT code, name FROM user WHERE name='苏三') enables a covering index scan, which is much faster.

EXPLAIN SELECT * FROM user WHERE name='苏三';

EXPLAIN SELECT code, name FROM user WHERE name='苏三';

This demonstrates the benefit of a covering index.

3. Calculations on Indexed Columns

Applying arithmetic to an indexed column disables the index. Example:

EXPLAIN SELECT * FROM user WHERE id+1=2;

4. Functions on Indexed Columns

Using a function such as SUBSTR on an indexed column also prevents index usage.

EXPLAIN SELECT * FROM user WHERE SUBSTR(height,1,2)=17;

5. Data‑type Mismatch

When the column type is VARCHAR but the query supplies an integer without quotes, MySQL cannot use the index because of the type mismatch.

EXPLAIN SELECT * FROM user WHERE code=101;

Conversely, an INT column works with or without quotes because MySQL implicitly converts the string to an integer.

6. LIKE Patterns

Indexes are used when the wildcard appears on the right side ( '10%') but not when it appears on the left ( '%1') or both sides ( '%1%').

EXPLAIN SELECT * FROM user WHERE code LIKE '10%';

EXPLAIN SELECT * FROM user WHERE code LIKE '%1';

7. Column Comparison

Comparing two indexed columns directly (e.g., WHERE id = height) forces a full scan because MySQL cannot use separate indexes simultaneously.

EXPLAIN SELECT * FROM user WHERE id = height;

8. OR Operator

If every column referenced by OR has an index, the query can still use indexes. Adding a non‑indexed column (e.g., address) makes all indexes ineffective.

EXPLAIN SELECT * FROM user WHERE id=1 OR height='175';

EXPLAIN SELECT * FROM user WHERE id=1 OR height='175' OR address='成都';

9. NOT IN and NOT EXISTS

NOT IN

on a primary‑key column can still use the index, but on a regular indexed column it forces a full scan. NOT EXISTS always results in a full scan in the examples shown.

EXPLAIN SELECT * FROM user WHERE height NOT IN (173,174,175,176);

EXPLAIN SELECT * FROM user WHERE id NOT IN (173,174,175,176);

EXPLAIN SELECT * FROM user t1 WHERE NOT EXISTS (SELECT 1 FROM user t2 WHERE t2.height=173 AND t1.id=t2.id);

10. ORDER BY Pitfalls

Indexes can be used for ORDER BY when the ordering columns follow the left‑most prefix of a composite index and a LIMIT clause is present. They are not used when:

No WHERE or LIMIT clause.

Ordering columns span different indexes.

The left‑most prefix rule is violated.

Mixed ASC/DESC directions are specified.

EXPLAIN SELECT * FROM user ORDER BY code LIMIT 100;

EXPLAIN SELECT * FROM user ORDER BY code, name;

EXPLAIN SELECT * FROM user ORDER BY name LIMIT 100;

EXPLAIN SELECT * FROM user ORDER BY code ASC, age DESC LIMIT 100;

Understanding these patterns helps keep MySQL queries fast and index‑friendly.