Understanding MySQL 8.0 Index Skip Scan (ISS)

MySQL 8.0 implements a feature called Index Skip Scan (ISS), which enables the optimizer to use a composite index even when the query predicates do not reference the leftmost column of that index.

Consider a table t1 with a composite index idx_u1(rank1, rank2) . A query such as SELECT * FROM t1 WHERE rank2 = 30 would normally require a full table scan because the leftmost column rank1 is not used.

ISS allows the optimizer to scan the index by skipping the leftmost column, avoiding the need to create a separate index on rank2 and reducing write overhead.

Example data (simplified):

rank1, rank2
1 100
1 200
1 300
1 400
1 500
1 600
5 100
5 200
5 300
5 400
5 500

Original SQL:

SELECT * FROM t1 WHERE rank2 > 400;

After MySQL applies ISS, the optimizer rewrites the query to include the leftmost column with a DISTINCT operation:

SELECT * FROM t1 WHERE rank1 = 1 AND rank2 > 400
UNION ALL
SELECT * FROM t1 WHERE rank1 = 5 AND rank2 > 400;

The optimizer internally adds a DISTINCT on rank1 and then scans the index for each distinct value.

Performance test with a low‑cardinality rank1 (few distinct values) shows that ISS dramatically reduces the number of rows scanned. The execution plans are illustrated below:

When ISS is disabled, the plan reverts to a full table scan:

Conversely, if the leftmost column has high cardinality (many distinct values), MySQL does not choose ISS and falls back to a full index scan, indicating that a separate index on rank2 is required. The relevant plans are shown:

In summary, Index Skip Scan is a MySQL 8.0 optimization strategy that is most effective when the leftmost column of a composite index has a small number of distinct values, such as gender or status columns.