Common MySQL Index Failure Scenarios and How to Diagnose Them

In an interview setting, the candidate discusses various MySQL index failure scenarios and ways to detect and mitigate them.

Table definition used for examples:

CREATE TABLE `tb_staff` (
  `staff_id` tinyint(3) NOT NULL COMMENT '员工编号',
  `id_no` varchar(20) DEFAULT NULL COMMENT '员工姓名',
  `name` varchar(20) DEFAULT NULL COMMENT '员工姓名',
  `email` varchar(200) DEFAULT NULL COMMENT '邮件地址',
  `age` tinyint(3) DEFAULT NULL COMMENT '年龄',
  `sex` tinyint(1) DEFAULT '0' COMMENT '0:男 1:女',
  `address` varchar(300) DEFAULT NULL COMMENT '家庭住址',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`staff_id`),
  KEY `id_no` (`id_no`),
  KEY `union_idno_name_email` (`id_no`,`name`,`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1. Leading wildcard in LIKE: SELECT * FROM tb_staff WHERE id_no LIKE '%8933'; The index on id_no cannot be used because the pattern starts with % .

2. NOT LIKE: SELECT * FROM db_staff WHERE id_no NOT LIKE '120%'; This also prevents index usage.

3. NOT IN: SELECT * FROM tb_staff WHERE id_no NOT IN ('xxxx','yyyy'); The optimizer cannot use the index.

4. NOT EXISTS: SELECT * FROM db_staff f WHERE NOT EXISTS (SELECT staff_id FROM db_staff_id a WHERE a.staff_id = f.staff_id); Indexes are not used.

5. Functions or implicit conversions in WHERE:

EXPLAIN SELECT * FROM db_staff WHERE staff_id + 1 = 2;
EXPLAIN SELECT * FROM db_staff WHERE id_no = 110112202409881123;

Both queries bypass indexes because of the expression or type conversion.

6. Inequality operators (!= or <>): SELECT * FROM db_staff WHERE id_no != 'somevalue'; Indexes are not used unless the column is a primary key.

7. IS NOT NULL: SELECT * FROM db_staff WHERE id_no IS NOT NULL; This condition cannot use an index.

8. OR conditions: SELECT * FROM db_staff WHERE staff_id = 2 OR id_no = '4'; The index is not used unless all OR branches reference indexed primary‑key columns, in which case it can be optimized.

9. Large result sets: When the filtered rows occupy a large proportion of the table, MySQL may choose a full table scan. Using additional filters or FORCE INDEX can compel index usage, e.g. SELECT * FROM db_staff FORCE INDEX(union_idno_name_email);

10. ORDER BY mismatches: If the ORDER BY column differs from the indexed column used in WHERE , the index may be ignored. Example:

SELECT id_no, name, email FROM db_staff WHERE id_no > '110112202409881120' ORDER BY create_time;

Similarly, GROUP BY can suffer the same issue unless a covering index includes both the filter and sort columns.

11. Execution plan attributes (EXPLAIN):

type – access type (system, const, eq_ref, ref, range, index, ALL)

key – index actually used

key_len – length of the index used

rows – estimated rows examined

Extra – additional info (Using where, Using filesort, Using index, etc.)

12. eq_ref vs ref: eq_ref occurs when each row from the outer table matches at most one row in the inner table via a primary or unique key (e.g., joining on staff_id ). ref is used with non‑unique indexes where multiple rows may match.

Understanding these patterns helps developers write queries that fully exploit MySQL indexes and avoid costly full‑table scans.