Common MySQL Index Failure Scenarios and Optimization Techniques

The article explains why SQL queries often lead to index inefficiency and how to avoid it.

It first prepares a sample table with three indexes (a composite index on sname, s_code, address , a primary key on id , and a regular index on height ) and provides the DDL and sample data.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `s_code` int(100) NULL DEFAULT NULL,
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `height` double NULL DEFAULT NULL,
  `classid` int(11) NULL DEFAULT NULL,
  `create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `普通索引`(`height`) USING BTREE,
  INDEX `联合索引`(`sname`,`s_code`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 CHARSET=utf8;
INSERT INTO `student` VALUES (1,'学生1',1,'上海',170,1,'2022-11-02 20:44:14');
INSERT INTO `student` VALUES (2,'学生2',2,'北京',180,2,'2022-11-02 20:44:16');
INSERT INTO `student` VALUES (3,'变成派大星',3,'京东',185,3,'2022-11-02 20:44:19');
INSERT INTO `student` VALUES (4,'学生4',4,'联通',190,4,'2022-11-02 20:44:25');
SET FOREIGN_KEY_CHECKS = 1;

It then discusses the leftmost‑prefix principle for composite indexes, showing which queries can use the index and which cannot, with several example SELECT statements.

-- Composite index sname,s_code,address
SELECT create_time FROM student WHERE sname = "变成派大星";  -- uses index
SELECT create_time FROM student WHERE s_code = 1;               -- does not use index (no leftmost column)
SELECT create_time FROM student WHERE address = "上海";          -- does not use index
SELECT create_time FROM student WHERE address = "上海" AND s_code = 1; -- does not use index
SELECT create_time FROM student WHERE sname = "变成派大星" AND address = "上海"; -- uses index up to sname
SELECT create_time FROM student WHERE sname = "变成派大星" AND s_code = 1 AND address = "上海"; -- uses index fully

The article explains that range conditions ( > , < , BETWEEN , LIKE with a leading wildcard) stop the prefix matching, and shows EXPLAIN output for both index‑using and full‑table‑scan cases.

EXPLAIN SELECT create_time FROM student WHERE sname = "变成派大星";  -- index used
EXPLAIN SELECT create_time FROM student WHERE address = "上海" AND s_code = 1; -- full scan

Further sections cover common index‑breaking patterns such as:

Using SELECT * (adds a back‑table lookup and extra columns).

Applying functions or arithmetic on indexed columns (prevents index use).

LIKE patterns: leading % disables index, trailing % may use it with lower selectivity.

OR conditions where only one side is indexed (causes full scan).

IN/NOT IN with large value sets (index may be ignored when selectivity is low).

ORDER BY that forces a sort, sometimes preferring a full scan over index + back‑table.

Subqueries that may or may not use indexes depending on rewrite.

The article notes that MySQL 8.0 introduces index jump scan, which can sometimes bypass the leftmost rule when the first column has low cardinality.

It also introduces Index Condition Pushdown (ICP), a feature added in MySQL 5.6 that pushes filtering to the secondary index, reducing the number of rows that need to be fetched from the primary index. An example query is shown:

SELECT * FROM table1 WHERE b LIKE '3%' AND c = 3;

ICP works only with composite indexes, cannot be used under subqueries or with stored functions, and its usage can be verified via the EXPLAIN output.

Each topic ends with a brief bullet‑point summary highlighting the key takeaway.