Index Condition Pushdown, Loose/Tight Index Scans, and Skip-Scan Concepts

Learning outcomes

Sometimes a query becomes faster without a new index because MySQL finds a more efficient way to use the index you already have. That does not make these access methods tuning switches to force everywhere. They are optimizer strategies selected when their structural prerequisites and estimated cost make sense.

Index Condition Pushdown: reject from the secondary entry first

Create an index where the first two parts form a useful range and a later indexed column can reject candidates before InnoDB fetches the full clustered row:

USE servicehub_index_lab;CREATE INDEX ix_wo_icp  ON work_orders (tenant_id, opened_at, status);ANALYZE TABLE work_orders;EXPLAINSELECT work_order_id,customer_name,detailsFROM work_ordersWHERE tenant_id=17  AND opened_at BETWEEN '2026-02-01' AND '2026-05-31'  AND status='open';

The range can use tenant_id and opened_at. The later status value is present in the secondary index entry. With Index Condition Pushdown (ICP), MySQL can push the index-checkable condition into the storage engine so entries failing status='open' can be rejected before full rows are fetched for customer_name and details. Traditional EXPLAIN reports Using index condition when ICP is used.

SET @saved_optimizer_switch = @@SESSION.optimizer_switch;SET SESSION optimizer_switch='index_condition_pushdown=off';EXPLAIN ANALYZESELECT work_order_id,customer_name,details FROM work_ordersWHERE tenant_id=17 AND opened_at BETWEEN '2026-02-01' AND '2026-05-31' AND status='open';SET SESSION optimizer_switch='index_condition_pushdown=on';EXPLAIN ANALYZESELECT work_order_id,customer_name,details FROM work_ordersWHERE tenant_id=17 AND opened_at BETWEEN '2026-02-01' AND '2026-05-31' AND status='open';SET SESSION optimizer_switch=@saved_optimizer_switch;

Record actual rows and local timing, but do not expect a dramatic difference on 20,000 warm rows. ICP’s mechanism is reducing unnecessary full-row reads. MySQL 8.4 documents ICP for range, ref, eq_ref, and ref_or_null access where full rows are needed; for InnoDB, ICP applies to secondary indexes, not the clustered index.

Loose Index Scan: jump between groups

A Loose Index Scan can exploit ordered keys so MySQL does not read every key in every group. Its eligibility is strict. A classic case groups by a leftmost prefix and asks for MIN()/MAX() of the immediately following index part.

CREATE INDEX ix_wo_tenant_opened  ON work_orders (tenant_id,opened_at);ANALYZE TABLE work_orders;EXPLAINSELECT tenant_id, MIN(opened_at), MAX(opened_at)FROM work_ordersGROUP BY tenant_id;

If selected, traditional EXPLAIN reports Using index for group-by. The important reasoning is why it is eligible: one table, group columns form a leftmost index prefix, and the MIN/MAX column follows that prefix. Change the query to SUM(priority) and this particular Loose Index Scan rule no longer applies.

EXPLAINSELECT tenant_id, SUM(priority)FROM work_ordersGROUP BY tenant_id;

Tight Index Scan: fill key gaps, then group

A Tight Index Scan may still use an ordered index for grouping even when the looser group-jumping strategy does not apply. It reads all keys in the qualifying range and groups after the scan. Equality predicates can fill leading key parts that would otherwise be gaps.

EXPLAINSELECT status, COUNT(*)FROM work_ordersWHERE tenant_id=17GROUP BY status;

The dashboard index begins with (tenant_id,status,...). Fixing tenant_id=17 supplies the leading constant, so rows for status are encountered in index order. MySQL does not expose a literal “Tight Index Scan” label in the same way it exposes Using index for group-by for Loose Index Scan; reason from the key, range, and whether a temporary/sort step is needed.

Skip scan: use later key parts by iterating leading values

Ordinary leftmost-prefix rules still hold. Skip scan is a special range access method that can make a composite index useful when the leading key part lacks a query predicate but has relatively few distinct values. Conceptually, the optimizer performs multiple ranges—one per relevant leading value—and applies a range on a later key part.

CREATE INDEX ix_wo_status_opened  ON work_orders(status,opened_at,work_order_id);ANALYZE TABLE work_orders;SHOW VARIABLES LIKE 'optimizer_switch';EXPLAINSELECT status,opened_at,work_order_idFROM work_ordersWHERE opened_at >= '2026-06-15'  AND opened_at <  '2026-07-01';

If MySQL chooses skip scan, traditional EXPLAIN shows Using index for skip scan. It may instead choose a full/index scan because skip scan is cost-based. The lesson is not to force it until the label appears; the lesson is that a small number of leading-key values can make multiple later-key ranges cheaper than scanning everything.

SET @saved_optimizer_switch2 = @@SESSION.optimizer_switch;SET SESSION optimizer_switch='skip_scan=off';EXPLAIN SELECT status,opened_at,work_order_id FROM work_ordersWHERE opened_at >= '2026-06-15' AND opened_at < '2026-07-01';SET SESSION optimizer_switch=@saved_optimizer_switch2;

Use that OFF experiment to understand whether skip scan changes the candidate plan on your data. Do not leave optimizer switches changed globally just to preserve a lab plan.

Tempting but ineffective: FORCE INDEX until EXPLAIN looks “indexed”

FORCE INDEX can be useful for diagnosis, but forcing an index because type=ALL looks visually bad can increase work. A table scan can be rational when a predicate returns most rows; a forced index can add random clustered lookups on top of scanning many secondary entries.

EXPLAIN ANALYZE SELECT work_order_id,details FROM work_orders WHERE status IN ('open','assigned','waiting','closed');EXPLAIN ANALYZE SELECT work_order_id,details FROM work_orders FORCE INDEX(ix_wo_status_opened) WHERE status IN ('open','assigned','waiting','closed');

Record the actual iterator work. If the forced plan is worse, you have disproved the slogan “an index scan is always better.” If it is better on your machine, that is still only one local observation; Chapter 09 will address statistics and plan regressions more systematically.

Evidence checklist

Production judgment and bridge

Optimizer features are not trophies to maximize. ICP, loose/tight scans, and skip scan are useful when they reduce work for real query shapes. Keep default cost-based behavior unless repeated representative measurements justify an exception, and always restore session experiments.

Lesson 5 combines everything into an index portfolio: which indexes are worth keeping for the workload as a whole, which overlap, which only look useful in synthetic tests, and how to account for write/storage cost before removing or adding anything.

Authoritative references

• MySQL 8.4 Reference Manual — Index Condition Pushdown
• MySQL 8.4 Reference Manual — GROUP BY Optimization
• MySQL 8.4 Reference Manual — Range Optimization / Skip Scan
• MySQL 8.4 Reference Manual — EXPLAIN Output
• MySQL 8.4 Reference Manual — Switchable Optimizations