How MySQL’s Skip Scan Defies the Leftmost Prefix Rule

Leftmost‑Prefix Principle

In MySQL a composite index (a,b,c) can be used only when the query predicates start with the leftmost column and proceed in order. The index can be used for predicates on a alone, on a and b, or on a,b,c. Predicates that start with b or c alone cannot use the index unless other conditions apply.

Choosing the Leading Column

The leading column should have high selectivity, but query frequency also matters. If column b is used in 80 % of queries while column a appears in only 10 %, it may be better to make b the leading column even if its distinct values are fewer.

Skip Scan Range Access Method (MySQL 8.0.13+)

From MySQL 8.0.13 the optimizer can apply the Skip Scan Range Access Method. When the leading column has low cardinality, the optimizer enumerates each distinct value of the leading column and performs an index range scan for each value. This allows a composite covering index to be used even when the query does not follow the leftmost‑prefix order.

Demonstration

Step 1 – Create table and composite index

CREATE TABLE `table1` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `a` INT DEFAULT NULL,
  `b` INT DEFAULT NULL,
  `c` INT DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

Step 2 – Insert initial data

INSERT INTO table1 (a,b,c) VALUES
  (1,1,1), (1,2,2), (1,3,3), (1,4,4), (1,5,5),
  (2,1,1), (2,2,2), (2,3,3), (2,4,4), (2,5,5);

Column a has only two distinct values (1,2); columns b and c each have five distinct values.

Step 3 – Expand the table

INSERT INTO table1 (a,b,c) SELECT a, b+5,  c+5  FROM table1;
INSERT INTO table1 (a,b,c) SELECT a, b+10, c+10 FROM table1;
INSERT INTO table1 (a,b,c) SELECT a, b+20, c+20 FROM table1;
INSERT INTO table1 (a,b,c) SELECT a, b+40, c+40 FROM table1;

After these inserts the table contains 160 rows, but column a still holds only the values 1 and 2.

Step 4 – Verify index usage without leftmost prefix

EXPLAIN SELECT * FROM table1 WHERE b = 5;

Before statistics are updated the plan shows type = index, meaning a full‑index scan (the index covers all columns). No skip‑scan is reported.

Step 5 – Update statistics and re‑explain

ANALYZE TABLE table1;
EXPLAIN SELECT * FROM table1 WHERE b = 5;

The Extra column now contains “Using index for skip scan”, confirming that the Skip Scan Range Access Method is active.

Step 6 – How skip scan works

For the query WHERE b = 5 the optimizer enumerates the two possible values of the leading column a (1 and 2) and executes two index range scans:

SELECT * FROM table1 WHERE a = 1 AND b = 5;
SELECT * FROM table1 WHERE a = 2 AND b = 5;

This approach is efficient when the leading column has low cardinality; the number of sub‑scans grows with the number of distinct values.

Conditions for Skip Scan

A composite covering index must exist.

The query must be a single‑table SELECT (no JOIN).

The query must not contain DISTINCT or GROUP BY.

The index must be able to satisfy the query (covering).

All predicate values must be constants.

The most restrictive requirement is that the index must be covering; the optimizer cannot fall back to a table lookup.

Practical considerations

Skip scan is most beneficial when the leading column has very low cardinality (e.g., a boolean or a small enumeration). If the leading column has high cardinality, the number of generated sub‑scans can outweigh any performance gain.

Conclusion

The Skip Scan Range Access Method extends the usefulness of composite indexes in MySQL 8.0.13 and later by allowing queries that do not follow the leftmost‑prefix order, provided the index is covering and the leading column has low cardinality. Understanding its prerequisites and performance trade‑offs helps in designing efficient indexes.