Mastering MySQL’s Leftmost Prefix Rule: When Indexes Work and When They Fail

Preparation

We create a student table with three indexes: a composite index on sname, s_code, address, a primary key on id, and a regular index on height. Sample data is inserted for testing.

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) NOT NULL,
  `s_code` int(100) DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `height` double DEFAULT NULL,
  `classid` int(11) 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 DEFAULT 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;

Leftmost Prefix Principle

The principle states that a composite index can be used only if the query filters start with the leftmost column of the index and continue with consecutive columns. As soon as a range condition (>, <, BETWEEN, LIKE) appears, matching stops for subsequent columns.

Examples:

Query on sname alone uses the index.

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

Query on address alone also cannot use the composite index.

Query on sname and s_code uses the index (optimizer can reorder columns).

Query on address and s_code cannot use the index because the leftmost column is missing.

MySQL may reorder equality conditions to match the index order, but any range condition stops further matching.

Index Types Without and With Range Conditions

When all conditions are equality, the optimizer can use the index as ref. When a range condition appears, the type becomes range, which may be less efficient.

SELECT *

Using SELECT * does not inherently break indexes; however, it forces a “back‑table” lookup after the index filter, adding extra I/O. It is better to select only needed columns to reduce overhead.

Functions in WHERE

Applying functions directly to indexed columns (e.g., WHERE b - 1 = 6) prevents index usage because the stored index values are raw column values. MySQL 8.0 introduces functional indexes that can index the result of a function.

LIKE Patterns

LIKE '%text%'

cannot use an index because the leading wildcard prevents prefix matching. LIKE 'text%' can use the index but may have a low selectivity, resulting in a low index cost.

OR Conditions

If an OR combines an indexed column with a non‑indexed column, MySQL may abandon the index and perform a full table scan. Adding indexes to all columns involved in the OR allows the optimizer to use them.

IN Clause

The IN operator uses the index, but when the list is large (typically >30% of rows), the optimizer may switch to a full scan.

ORDER BY

When ordering by an indexed column, MySQL may still choose a full table scan if the cost of using the index plus a “back‑table” lookup exceeds the cost of scanning and sorting the whole table.

Subqueries

Subqueries can use indexes, but poorly written subqueries may cause full scans. Proper rewriting can preserve index usage.

Final Summary

Indexes are effective when queries respect the leftmost prefix, avoid range conditions on early columns, select only needed columns, and use functions or patterns that allow index usage. Understanding these rules helps avoid unnecessary full‑table scans and improves SQL performance.