Common Cases When MySQL Indexes Are Ignored

Indexes are a powerful tool for MySQL query optimization, but improper use can turn them into a performance liability, forcing the optimizer to perform full table scans. The article first creates a test table t_student with a primary key and several varchar, tinyint, and timestamp columns, then adds a single‑column index on first_name and a composite index on first_name, last_name. Sample data (100 000 rows) is generated via a stored procedure that builds random first and last names.

After confirming that a simple equality query uses the index, the article lists nine common situations where MySQL ignores indexes:

Leading‑wildcard LIKE : queries like WHERE first_name LIKE '%tom' cannot use the index because the pattern starts with a wildcard.

Arithmetic on indexed columns : expressions such as WHERE id+1 = 1024 prevent index usage, while rewriting as WHERE id = 1023 allows it.

Functions on indexed columns : using functions like SUBSTR(first_name,2,1) = 'i' forces a full scan.

Data‑type mismatch : comparing a varchar column with a numeric literal (e.g., WHERE first_name = 20) disables the index.

Composite index not using the leftmost column : a query that filters only on last_name cannot use the (first_name, last_name) index.

Different character sets : converting column and literal to different encodings (e.g.,

CONVERT(first_name USING utf8mb4) = CONVERT('tom' USING latin1)

) makes the index ineffective.

OR condition with a non‑indexed column : WHERE first_name='tom' OR grade=5 leads to a full scan because one operand lacks an index.

Other patterns : operators like !=, <>, NOT IN, IS NULL, range queries, or optimizer hints such as IGNORE INDEX can also cause index bypass.

The article concludes by inviting readers to discuss additional cases that may cause index failure.

Code examples used in the article:

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;

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

DROP PROCEDURE IF EXISTS PROC_INSERT_STUDENT;
DELIMITER //
CREATE PROCEDURE PROC_INSERT_STUDENT()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE firstName, lastName, randomChar 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();

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

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

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

EXPLAIN SELECT * FROM t_student WHERE first_name=20;

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

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

EXPLAIN SELECT * FROM t_student WHERE first_name='tom' OR grade=5;