Common Scenarios Where MySQL Indexes Fail

In Java development interview positions, questions about database indexes are almost always asked, regardless of company size or type.

Among index‑related questions, interviewers often probe scenarios where indexes become ineffective, which is also a crucial point for everyday development.

Preparation

First, a t_student table is created:

CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `first_name` varchar(10) NOT NULL COMMENT '名字',
  `last_name` varchar(32) NOT NULL COMMENT '姓氏',
  `gender` varchar(10) NOT NULL COMMENT '性别',
  `grade` tinyint(1) NOT NULL COMMENT '年级',
  `enroll_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Two indexes are added: one on first_name and a composite index on first_name, last_name :

ALTER TABLE `t_student`
ADD INDEX `idx_first_name` (`first_name`) USING BTREE,
ADD INDEX `idx_full_name` (`first_name`, `last_name`) USING BTREE;

A stored procedure inserts 100,000 rows with random names, genders, grades and timestamps. After the data is populated, the execution plan is checked to confirm the indexes work.

1. LIKE queries starting with %

Using a leading wildcard prevents the index from being used:

EXPLAIN SELECT * FROM t_student WHERE first_name LIKE '%tom';

The plan shows type=ALL , indicating a full table scan.

2. Calculations on indexed columns

Applying arithmetic on an indexed column disables the index:

EXPLAIN SELECT * FROM t_student WHERE id+1=1024;

The plan again results in a full scan.

3. Using functions on indexed columns

Functions such as SUBSTR cause index loss:

EXPLAIN SELECT * FROM t_student WHERE SUBSTR(first_name,2,1)='i';

The optimizer chooses a full scan.

4. Data type mismatch

Comparing a string column with a numeric literal makes the index ineffective:

EXPLAIN SELECT * FROM t_student WHERE first_name=20;

The plan shows a full scan.

5. Composite index not using the first column

If a query filters only on the second column of a composite index, the index is ignored:

EXPLAIN SELECT * FROM t_student WHERE last_name='tom';

The result is a full table scan.

6. Different string encodings

Comparing values with different character sets also disables the index:

EXPLAIN SELECT * FROM t_student WHERE CONVERT(first_name USING utf8mb4)=CONVERT('tom' USING latin1);

This again leads to a full scan.

Summary

The article lists the above common index‑failure scenarios and mentions additional cases such as using != , <> , NOT IN , IS NULL , range queries, or optimizer‑chosen full scans. It also notes that MySQL hints like IGNORE INDEX can force the optimizer to skip an index.

Creative effort is appreciated – please like, follow, or comment if you have questions.