Skip Scan — range access without the leading index columnMySQL 8.4

How MySQL turns a full table scan into N small range scans by iterating over distinct values of the leading index column.

Parameters (Skip Scan)

Total number of rows in the table.
Number of distinct values in column A (e.g. gender). Lower = better for Skip Scan.
Percentage of rows matching WHERE condition on trailing column B.

Example query this animation executes

-- Index on (gender, age) but no equality on gender
SELECT * FROM employees
WHERE  age BETWEEN 25 AND 30;

Skip Scan iterates over each distinct gender value, doing a range scan on age within each group — avoids reading the whole table.

What you'll see in the animation

  • A composite index on (gender, age) is shown as colored groups.
  • The cursor pill jumps to the first distinct gender value.
  • Within each group, a range scan checks age BETWEEN 25 AND 30.
  • Matching rows are highlighted green; non-matching rows are greyed out.
  • The cursor hops to the next group and repeats until all groups are scanned.
Ready — press Play
0.0s 0.0s

Cost readout (Skip Scan)

Distinct values (leading col) ?Number of distinct values in the leading index column. Skip Scan does one sub-range-scan per distinct value.

—

Rows per group ?table_rows / NDV of leading column. Each group is scanned separately.

—

Matching rows per group ?Rows per group that satisfy the WHERE condition on the trailing column.

—

Total matching rows ?Sum of matching rows across all groups.

—

Skip scan reads ?Total reads: for each distinct value, one B+tree seek plus a range read of matching rows.

—

Full scan reads ?A full table scan reads every row — the baseline without Skip Scan.

—

Savings ?Reads saved compared to a full table scan: full_scan_reads minus skip_scan_reads.

—

Reads vs table size (log–log)

Learn more — when does the optimizer choose Skip Scan?

Skip Scan was introduced in MySQL 8.0.13 and is controlled by optimizer_switch='skip_scan=on' (on by default).

When it helps:

1. The leading column of a composite index has low NDV (number of distinct values) — e.g. gender, status, boolean flags.

2. The trailing column has a selective range condition (e.g. age BETWEEN 25 AND 30).

3. The optimizer estimates that doing N sub-range-scans (one per distinct leading value) is cheaper than a full table scan or a full index scan.

When it does NOT help:

1. The leading column has high NDV (thousands of distinct values) — too many sub-scans make it slower than a full scan.

2. The range on the trailing column is not selective (most rows match) — you end up reading almost everything anyway.

3. A better single-column index on the trailing column exists.

Note: Skip Scan is a MySQL-only optimization. MariaDB does not implement it as of 11.x. In EXPLAIN output you will see Using index for skip scan in the Extra column.

Source: MySQL 8.4 Reference Manual §10.2.1.2 “Range Optimization — Skip Scan Range Access Method”.