Why MySQL Indexes Fail and How to Make Them Work

When reviewing interview questions, MySQL index failure is a familiar topic; many interviewers ask about it. This article collects and expands on common scenarios where MySQL indexes become ineffective, helping readers understand and resolve these issues.

Basic Data Preparation

A sample table student is created with three indexes: a composite index on sname, s_code, address; a primary key on id; and a single‑column index on height.

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(),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `普通索引`(`height`) USING BTREE,
  INDEX `联合索引`(`sname`,`s_code`,`address`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 CHARACTER SET utf8 COLLATE=utf8_general_ci ROW_FORMAT=Dynamic;
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');

Problem Thinking

Several SELECT statements are presented to test whether the composite index is used:

-- Composite index sname,s_code,address
1) SELECT create_time FROM student WHERE sname = "变成派大星"; -- ?
2) SELECT create_time FROM student WHERE s_code = 1;               -- ?
3) SELECT create_time FROM student WHERE address = "上海";        -- ?
4) SELECT create_time FROM student WHERE address = "上海" AND s_code = 1; -- ?
5) SELECT create_time FROM student WHERE address = "上海" AND sname = "变成派大星"; -- ?
6) SELECT create_time FROM student WHERE sname = "变成派大星" AND address = "上海"; -- ?
7) SELECT create_time FROM student WHERE sname = "变成派大星" AND s_code = 1 AND address = "上海"; -- ?

Readers are asked to predict which queries will use the index.

Left‑most Prefix Principle

The left‑most prefix rule states that a composite index can be used only if the query references the leftmost columns consecutively. Once a range condition (>, <, BETWEEN, LIKE) appears, matching stops for subsequent columns.

Examples:

Query on sname alone uses the index.

Query on s_code alone does not use the index because sname (the leftmost column) is missing.

Query on sname and s_code uses the index; the optimizer can reorder the conditions.

Index Usage Examples

Using EXPLAIN to show index usage:

EXPLAIN SELECT create_time FROM student WHERE sname = "变成派大星";

Result shows the query uses the index.

EXPLAIN SELECT create_time FROM student WHERE address = "上海" AND s_code = 1;

This query performs a full table scan (rows = 4) because the leftmost column sname is not referenced.

Why the Left‑most Prefix Matters

In a B+‑tree, the first column determines the primary ordering; subsequent columns are ordered only within groups sharing the same first‑column value. If the first column is not used, later columns appear unordered and cannot be efficiently searched.

Additional Scenarios

Index Jump Scan (MySQL 8.0)

MySQL 8.0 can skip the leftmost column when its cardinality is low, allowing the composite index to be used even if the first column is omitted.

Select *

Although SELECT * can still use an index, it forces a back‑table lookup, adding overhead. Limiting selected columns reduces this cost.

Functions and Calculations

Applying functions to indexed columns (e.g., WHERE YEAR(date) = 2022) prevents index usage unless a functional index is defined (available from MySQL 8.0).

LIKE Patterns

Patterns starting with a wildcard (e.g., LIKE '%text%') cannot use the index, while patterns with a fixed prefix (e.g., LIKE 'text%') may use it, though the index rank may be low due to broad range.

OR Conditions

If one side of an OR uses an indexed column and the other does not, the optimizer may abandon the index. Adding indexes to both sides restores index usage.

IN and NOT IN

IN

uses the index, but when the list is large (e.g., >30% of rows) the optimizer may switch to a full scan. NOT IN behaves similarly.

ORDER BY

MySQL may choose a full table scan over an index‑based sort when the cost of back‑table lookups outweighs the benefit of the pre‑sorted index.

Subqueries

Subqueries can use indexes, but poor formulation can prevent it.

Reducing Back‑Table Lookups (ICP)

Index Condition Pushdown (ICP) introduced in MySQL 5.6 pushes filter conditions down to the secondary index scan, reducing the number of rows that need to be fetched from the primary key. ICP works best with composite indexes and cannot be used with subqueries or stored functions.

Big Summary

The article concludes with a comprehensive diagram summarizing all discussed index‑inefficiency cases and optimization techniques.