Parameters (Skip Scan)
About this exampleAbout this example
Example query this animation executes
-- idx_gender_age (gender, age), no condition on gender SELECT id, gender, age FROM employees WHERE age BETWEEN 25 AND 30;
Skip scan visits each distinct gender value and reads the 25 to 30 range of age inside it, instead of reading the whole index. MySQL plans it only when the index holds every column the query reads (InnoDB adds the primary key id to every index), so SELECT * would not qualify. EXPLAIN's Extra column shows Using index for skip scan.
What you'll see in the animation
- Each box is the run of idx_gender_age entries for one gender, in index order: all F entries, then all M entries, each sorted by age.
- The orange bar is the scan's position. It first reads one entry to learn the gender value (orange), then seeks straight to age 25 inside that gender.
- Entries with ages 25 to 30 turn green and are sent. The first entry past 30 (blue) ends that gender's range.
- Dashed grey entries are never read: the seeks jump over them. Then the scan seeks past the whole gender to the next one.
- Next step shows one seek or one range at a time, and the simplified code beside the drawing highlights the lines each step runs.
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 query reads only columns in the index (here id,
gender and age; InnoDB keeps the primary key in
every index). SELECT * cannot use a skip scan.
4. The optimizer estimates that doing N sub-range-scans (one per distinct leading value, each starting with two seeks) 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 has no skip scan access method. 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”.