Why MySQL Indexes Fail and How to Fix Them: 12 Common Scenarios

Understanding Index Failure

Indexes are ordered data structures that let MySQL locate rows quickly; when a query breaks the order, the index becomes ineffective and MySQL falls back to a full table scan.

Example Table

CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT COMMENT '用户ID（主键，自增）',
  username VARCHAR(50) NOT NULL COMMENT '用户名（唯一，普通索引）',
  phone VARCHAR(20) NOT NULL COMMENT '手机号（唯一，普通索引）',
  create_time DATETIME NOT NULL COMMENT '注册时间（普通索引）',
  gender CHAR(1) NOT NULL DEFAULT '0' COMMENT '性别（0-未知/1-男/2-女）',
  age INT DEFAULT NULL COMMENT '年龄',
  email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
  status TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态（1-正常/0-禁用）',
  PRIMARY KEY (id),
  KEY idx_username (username),
  KEY idx_phone (phone),
  KEY idx_create_time (create_time),
  KEY idx_username_age (username, age)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

12 Common Scenarios of Index Failure

1. Using Functions on Indexed Columns

Applying DATE(create_time) changes the column value, so MySQL cannot use idx_create_time. Use a range query on the raw column instead.

-- Wrong
SELECT * FROM user WHERE DATE(create_time) = '2024-01-01';

-- Correct
SELECT * FROM user WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59';

2. Arithmetic on Indexed Columns

Adding 1 to id destroys the index order.

-- Wrong
SELECT * FROM user WHERE id + 1 = 1000;

-- Correct
SELECT * FROM user WHERE id = 999;

3. Implicit Type Conversion

Comparing a numeric literal with a VARCHAR column forces conversion and disables the index.

-- Wrong
SELECT * FROM user WHERE phone = 13800138000;

-- Correct
SELECT * FROM user WHERE phone = '13800138000';

4. OR with Non‑Indexed Columns

If any operand of an OR lacks an index, MySQL cannot use the index.

-- Wrong
SELECT * FROM user WHERE username = 'zhangsan' OR age = 25;

-- Correct (add index on age)
ALTER TABLE user ADD INDEX idx_age (age);
-- or use UNION ALL
SELECT * FROM user WHERE username = 'zhangsan'
UNION ALL
SELECT * FROM user WHERE age = 25;

5. NOT IN / NOT EXISTS on Indexed Columns

MySQL usually ignores the index for NOT IN.

-- Better
CREATE TEMPORARY TABLE temp_names (name VARCHAR(50));
INSERT INTO temp_names VALUES ('zhangsan'), ('lisi');
SELECT u.* FROM user u LEFT JOIN temp_names t ON u.username = t.name WHERE t.name IS NULL;

6. LIKE with Leading Wildcard

LIKE '%san' cannot use the index; use a prefix pattern or a full‑text engine.

-- Correct prefix
SELECT * FROM user WHERE username LIKE 'zhang%';

7. Composite Index Not Using Leftmost Prefix

Querying only age on an index (username, age) skips the leftmost column and fails.

SELECT * FROM user WHERE username = 'zhangsan' AND age = 25;

8. Range Query on Leftmost Column Blocks Rightmost Column

When a range condition is on username, the following age condition cannot use the same composite index.

-- New index (age, username) solves it
ALTER TABLE user ADD KEY idx_age_username (age, username);
SELECT * FROM user WHERE age = 25 AND username LIKE 'zhang%';

9. Low‑Selectivity Index

Index on gender is useless because the column has only three distinct values.

ALTER TABLE user ADD INDEX idx_gender_age (gender, age);
SELECT * FROM user WHERE gender = '男' AND age = 25;

10. Redundant / Duplicate Indexes

Keeping both idx_username and idx_username_age wastes space; drop the redundant one.

DROP INDEX idx_username ON user;

11. Missing Covering Index

Querying columns not fully covered by a composite index forces a back‑table lookup.

ALTER TABLE user ADD INDEX idx_username_age_email (username, age, email);
SELECT username, age, email FROM user WHERE username = 'zhangsan';

12. Index Gaps After Massive Deletions

Large deletions leave empty index pages; run OPTIMIZE TABLE or delete in smaller batches.

OPTIMIZE TABLE user;

Using EXPLAIN to Diagnose Index Usage

Run EXPLAIN SELECT … and examine the type , key , rows , and Extra columns. type = ALL and key = NULL indicate a full scan; type = range and a non‑NULL key show the index is effective.

Conclusion

Index failure stems from three root causes: (1) operations that break the ordered nature of the indexed column, (2) query patterns that violate index matching rules, and (3) poor index design such as low‑selectivity or redundant indexes. Identify the cause, apply the appropriate fix, and verify with EXPLAIN.