Common Interview Question: When Does a MySQL Index Fail? Hands‑On Demo

In Java development interviews, database index questions are almost always asked. To illustrate why an otherwise well‑designed index may become useless, the article builds a small MySQL project and runs a series of experiments.

Preparation

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:

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 first and last names, random gender, and a random grade.

DROP PROCEDURE IF EXISTS PROC_INSERT_STUDENT;
DELIMITER //
CREATE PROCEDURE PROC_INSERT_STUDENT()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE firstName VARCHAR(64) DEFAULT '';
  DECLARE lastName VARCHAR(64) DEFAULT '';
  WHILE i <= 100000 DO
    SET firstName='';
    SET j=1;
    WHILE j<=4 DO
      SET randomChar = CHAR(FLOOR(RAND()*26)+97);
      SET firstName = CONCAT(firstName, randomChar);
      SET j=j+1;
    END WHILE;
    SET lastName='';
    SET k=1;
    WHILE k<=6 DO
      SET randomChar = CHAR(FLOOR(RAND()*26)+97);
      SET lastName = CONCAT(lastName, randomChar);
      SET k=k+1;
    END WHILE;
    INSERT INTO t_student(first_name, last_name, gender, grade, enroll_time)
    VALUES (firstName, lastName,
            CASE FLOOR(RAND()*100)%2 WHEN 0 THEN 'male' ELSE 'female' END,
            FLOOR(RAND()*100)%6+1,
            NOW());
    SET i=i+1;
  END WHILE;
END//
DELIMITER ;
CALL PROC_INSERT_STUDENT();

After the data is ready, the article checks that the indexes work correctly with a simple EXPLAIN query.

1. LIKE with a leading wildcard

Query:

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

The type column in the execution plan is ALL, indicating a full‑table scan because the pattern starts with %, which prevents index usage.

2. Arithmetic on an indexed column

Query:

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

Because the expression id+1 is computed, MySQL cannot use the index on id, resulting in a full scan.

3. Functions on an indexed column

Query:

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

Applying SUBSTR (or any function) to the indexed column disables index usage, so the plan again shows a full scan.

4. Data‑type mismatch

Query:

EXPLAIN SELECT * FROM t_student WHERE first_name=20;

The first_name column is a string, but the constant is numeric. MySQL must convert the column, causing the index to be ignored and a full scan to occur.

5. Composite index not using the leftmost column

Query:

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

The table has a composite index (first_name, last_name). Since the query filters only on last_name, the leftmost column is missing, so the index cannot be used.

6. Different string encodings

Query:

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

When the two sides of the comparison use different character sets, MySQL must convert the values, which prevents the index from being used, leading to a full scan.

Other common cases

Using !=, <> or NOT IN may bypass the index.

Conditions like IS NULL or IS NOT NULL on indexed columns can also lead to full scans.

Range queries (e.g., BETWEEN, >, <) sometimes prevent index usage depending on the optimizer.

If the optimizer estimates that a full table scan is cheaper than using the index, it will choose the scan.

The IGNORE INDEX hint can forcibly disable an index.

By understanding these scenarios, developers can avoid accidental index loss and write queries that truly benefit from MySQL’s indexing mechanisms.