15 Common MySQL Index Pitfalls and How to Avoid Them

Background

Whether you are a senior engineer or a beginner, you will occasionally encounter MySQL indexes that are not used. A common symptom is that an index has been added to a column but does not take effect. This article collects fifteen typical cases of index loss, demonstrates them with examples, and helps you avoid these pitfalls.

Database and Index Preparation

Create Table Structure

To verify index usage step by step, we first create a table t_user:

CREATE TABLE `t_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `id_no` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Identity number',
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Username',
  `age` int(11) DEFAULT NULL COMMENT 'Age',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  PRIMARY KEY (`id`),
  KEY `union_idx` (`id_no`,`username`,`age`),
  KEY `create_time_idx` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

The table has three indexes: id: primary key; union_idx: composite index on id_no, username, age; create_time_idx: ordinary index on create_time.

Initialize Data

Data is inserted in two parts: basic data and bulk import data via a stored procedure.

Four basic rows are inserted, with the fourth row having a future timestamp for later special‑case testing:

INSERT INTO `t_user` (`id`,`id_no`,`username`,`age`,`create_time`) VALUES (null,'1001','Tom1',11,'2022-02-27 09:04:23');
INSERT INTO `t_user` (`id`,`id_no`,`username`,`age`,`create_time`) VALUES (null,'1002','Tom2',12,'2022-02-26 09:04:23');
INSERT INTO `t_user` (`id`,`id_no`,`username`,`age`,`create_time`) VALUES (null,'1003','Tom3',13,'2022-02-25 09:04:23');
INSERT INTO `t_user` (`id`,`id_no`,`username`,`age`,`create_time`) VALUES (null,'1004','Tom4',14,'2023-02-25 09:04:23');

A stored procedure insert_t_user is provided for bulk insertion (not executed unless needed):

-- Drop existing procedure
DROP PROCEDURE IF EXISTS `insert_t_user`;

delimiter $

CREATE PROCEDURE insert_t_user(IN limit_num int)
BEGIN
  DECLARE i INT DEFAULT 10;
  DECLARE id_no varchar(18);
  DECLARE username varchar(32);
  DECLARE age TINYINT DEFAULT 1;
  WHILE i < limit_num DO
    SET id_no = CONCAT('NO', i);
    SET username = CONCAT('Tom', i);
    SET age = FLOOR(10 + RAND()*2);
    INSERT INTO `t_user` VALUES (NULL, id_no, username, age, NOW());
    SET i = i + 1;
  END WHILE;
END $
-- Call the procedure
CALL insert_t_user(100);

Database Version and Execution Plan

Current MySQL version (used in the examples) is 8.0.18:

SELECT VERSION();
8.0.18

Use EXPLAIN to view the execution plan and determine whether an index is used. EXPLAIN SELECT * FROM t_user WHERE id = 1; Result (example):

The query uses the primary key index ( PRIMARY) with key_len equal to 4 bytes. key_len indicates how many bytes of the index are actually used, which is crucial for composite indexes.

1. Composite Index Not Satisfying Left‑Most Matching

In a composite index, the leftmost column must appear in the WHERE clause for the index to be used.

Composite index definition: KEY `union_idx` (`id_no`,`username`,`age`) Example 1 (condition on id_no only):

EXPLAIN SELECT * FROM t_user WHERE id_no = '1002';

The query uses union_idx and only the id_no part of the index.

Example 2 (conditions on id_no and username):

EXPLAIN SELECT * FROM t_user WHERE id_no = '1002' AND username = 'Tom2';

The index is still used, covering both id_no and username.

Example 3 (conditions on id_no and age):

EXPLAIN SELECT * FROM t_user WHERE id_no = '1002' AND age = 12;

Only the id_no part is used.

Reverse example (condition on username and age without the leftmost column):

EXPLAIN SELECT * FROM t_user WHERE username = 'Tom2' AND age = 12;

No index is used.

2. Using SELECT *

Alibaba Development Manual (ORM Mapping) strongly recommends not using * in queries; specify required columns explicitly.

When SELECT * is used, MySQL cannot use a covering index even if the WHERE clause matches an index.

Example of a covering index query:

EXPLAIN SELECT id_no, username, age FROM t_user WHERE username = 'Tom2';
EXPLAIN SELECT id_no, username, age FROM t_user WHERE age = 12;

Both queries use the index because the selected columns are all indexed.

3. Index Column Involved in Arithmetic

Example:

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

The arithmetic prevents index usage, causing a full table scan. Compute the value beforehand instead:

EXPLAIN SELECT * FROM t_user WHERE id = 1; -- memory‑calculated
EXPLAIN SELECT * FROM t_user WHERE id = 2 - 1; -- parameter‑side calculation

4. Index Column Used with a Function

Example:

EXPLAIN SELECT * FROM t_user WHERE SUBSTR(id_no,1,3) = '100';

Using a function on the indexed column forces a full scan. Compute the value outside the query or avoid the function.

5. Incorrect LIKE Usage

Patterns with a leading wildcard cannot use an index:

LIKE '%abc'

LIKE '%abc%'

Example:

EXPLAIN SELECT * FROM t_user WHERE id_no LIKE '%00%';

Because the wildcard is at the start, the index is ignored.

6. Implicit Type Conversion

Example:

EXPLAIN SELECT * FROM t_user WHERE id_no = 1002;

The column id_no is VARCHAR, but the constant is an integer, causing an implicit conversion and a full scan. Use quotes to match the column type.

7. Using OR

When one side of an OR condition lacks an index, the whole query may forgo indexes:

EXPLAIN SELECT * FROM t_user WHERE id = 2 OR username = 'Tom2';

Similarly, range conditions combined with OR (e.g., id > 1 OR id < 80) also prevent index usage.

8. Comparing Two Indexed Columns

Even if both columns have indexes, a comparison like id > age disables index usage:

EXPLAIN SELECT * FROM t_user WHERE id > age;

9. Inequality ( <> or != )

When the result set is large, MySQL may ignore the index:

EXPLAIN SELECT * FROM t_user WHERE id_no <> '1002';

For primary key columns, inequality still uses the index.

10. IS NOT NULL

EXPLAIN SELECT * FROM t_user WHERE id_no IS NOT NULL;

IS NOT NULL

typically prevents index usage.

11. NOT IN and NOT EXISTS

Using NOT IN on a non‑primary indexed column leads to a full scan, while the same condition on a primary key uses the index.

EXPLAIN SELECT * FROM t_user WHERE id_no NOT IN ('1002','1003');
EXPLAIN SELECT * FROM t_user WHERE id NOT IN (2,3);
EXPLAIN SELECT * FROM t_user u1 WHERE NOT EXISTS (SELECT 1 FROM t_user u2 WHERE u2.id = 2 AND u2.id = u1.id);

NOT EXISTS

also disables the index.

12. ORDER BY Causing Index Loss

Sorting the whole table usually forces a full scan:

EXPLAIN SELECT * FROM t_user ORDER BY id_no;

Adding LIMIT does not guarantee index usage. However, ordering by a primary key ( ORDER BY id DESC) does use the index.

EXPLAIN SELECT * FROM t_user ORDER BY id DESC;

Multiple columns in ORDER BY (e.g., ORDER BY id, id_no DESC) often bypass the index.

13. Parameter Differences Leading to Index Loss

Range queries may or may not use the index depending on the selectivity of the constant value. For example, querying create_time > '2023-02-24 09:04:23' uses the index, while create_time > '2022-02-27 09:04:23' triggers a full scan because a larger portion of rows match.

EXPLAIN SELECT * FROM t_user WHERE create_time > '2023-02-24 09:04:23';
EXPLAIN SELECT * FROM t_user WHERE create_time > '2022-02-27 09:04:23';

The optimizer decides based on the estimated row count; if the proportion exceeds roughly 10‑30 % of the table, it may prefer a full scan.

14. Other Situations

Index behavior also depends on the index type (B‑tree vs. bitmap) and other optimizer strategies. When in doubt, run EXPLAIN to verify.

Conclusion

The article lists fifteen common MySQL index‑loss scenarios, noting that behavior can vary across MySQL versions. Most cases are deterministic; a few depend on optimizer heuristics. Keep this guide handy and use EXPLAIN to confirm index usage in practice.