Composite Index Anatomy

Read column order, range boundaries, ICP, and covering from real plans and rows

MySQL 8.4.102026-07-12

Learning guide

Question for this Lab

With the same composite-index columns, how does order change the equality prefix, first range, ICP, and row materialization?

Execution engine: MySQL 8.4.10

Who runs it?

One session executes the same SELECT with three index-order variants.

What stays controlled?

The same orders fixture with customer_id, status, ordered_at, and total_cents plus the same result checksum is fixed across all variants.

Executed SQL

SELECT id, customer_id, status, ordered_at, total_cents
FROM orders
WHERE customer_id = 412
  AND status = 'PAID'
  AND ordered_at >= '2025-01-01 00:00:00'
ORDER BY ordered_at DESC, id DESC
LIMIT 20;

What changes?

B-tree bounds, range candidates, ICP rejections, and whether base-row lookups are needed change with index order.

What stays fixed?

WHERE, ORDER BY, LIMIT 20, returned rows, and checksum remain identical across all three variants.

Predict before tracing

What happens when customer_id and status precede the ordered_at range?

The equality prefix is fixed first, narrowing the range candidates.B-tree bounds are independent of column order.Index-key order changes the returned result.

Evidence mission

Open the range-candidate step of the misordered variant and check how many fixture-derived candidates enter M_RANGE and how the predicate classifies them.

Where do you look?: Compare the range evidence in the composite-index lens with its candidate, pass, and reject classification.
Screen to open: Misordered · range candidates

What should you see?

Expected observation

M_RANGE is the exact set of 31,348 fixture-derived candidates satisfying status='PAID' and the ordered_at lower bound; 31 candidates with customer_id=412 pass and 31,317 are rejected. These counts are not physical pages or entries visited by the engine.

Evidence boundary

Derived · fixture: M_RANGE candidate 31,348 with pass 31 and reject 31,317 classification

Checkpoint · explain from evidenceWhat do 31,348 candidates, 31 pass, and 31,317 reject mean for the misordered fixture?Reveal answer and basis

They classify candidate entries formed by the misordered range in the fixed fixture. They are not a claim about physical pages or entries visited by the captured engine.

Evidence boundary

Derived · fixture: candidate, pass, and reject entry classification

Core takeaway

For a composite index, equality-prefix and first-range order plus entry coverage matter more than merely including every column.

Next connection

The next Lab compares how accurately statistics estimate the actual row distribution behind an access path.

Review terms

B-treeIntroduced in Lab 02: An index structure that stores ordered keys hierarchically to support equality and range lookups.
equality prefixIntroduced in Lab 02: The consecutive key parts fixed by equality predicates from the left edge of a composite index.
first rangeIntroduced in Lab 02: The first range key part that forms composite-index bounds; later parts may still contribute through filtering or ICP.
Index Condition Pushdown (ICP)Introduced in Lab 02: An optimization that evaluates eligible predicates from index-entry columns before fetching the base row.
covering indexIntroduced in Lab 02: An index that contains the columns needed by a query so a base-row lookup can be avoided.

01 · SOURCE

SQL

1 active fragmentMySQLLIMIT 20

SELECT id, customer_id, status, ordered_at, total_cents
FROM orders
WHERE customer_id = 412
  AND status = 'PAID'
  AND ordered_at >= '2025-01-01 00:00:00'
ORDER BY ordered_at DESC, id DESC
LIMIT 20;

02 · FLOW

Native Plan

01 / 12

Limit 20

Iterator actual rows: 20

Limit: 20 row(s)

idx_orders_status_date_customer range scan

Estimated rows: 60984

status = 'PAID' AND ordered_at <= '2025-01-01 00:00:00' AND customer_id <= 412

INDEX DEFINITION

Composite key parts

Derived · capture

01statusASC

Equality prefix

02ordered_atDESC

First range

03customer_idASC

Boundary suffixICP

04idDESC

ORDER BY

FRAME DETAILquery shape

Captured + Derived + Model

RANGE31,348

PASS31

REJECT31,317

row	status	ordered_at	customer_id	id
`order-131071`	PAID	2026-06-29 05:10:00	1023	131071
`order-131070`	PAID	2026-06-29 05:00:00	1022	131070
`order-131066`	PAID	2026-06-29 04:20:00	1018	131066
`order-131065`	PAID	2026-06-29 04:10:00	1017	131065
`order-131061`	PAID	2026-06-29 03:30:00	1013	131061
`order-131060`	PAID	2026-06-29 03:20:00	1012	131060
`order-131056`	PAID	2026-06-29 02:40:00	1008	131056
`order-131055`	PAID	2026-06-29 02:30:00	1007	131055
`order-131051`	PAID	2026-06-29 01:50:00	1003	131051
`order-131050`	PAID	2026-06-29 01:40:00	1002	131050
`order-131046`	PAID	2026-06-29 01:00:00	998	131046
`order-131045`	PAID	2026-06-29 00:50:00	997	131045

Logical tuple order · exact fixture rows

STEP · query shape

Read the SQL and index definition together

Trace customer_id=412, status='PAID', the ordered_at lower bound, descending order, and LIMIT 20 through one access path.

Plan facts

Captured

ACCESS: range
SELECTED KEY: idx_orders_status_date_customer
USED KEY PARTS: status → ordered_at
FILESORT: no

limitmisordered-limitLimit: 20 row(s)

index-range-scanmisordered-range-scanstatus = 'PAID' AND ordered_at <= '2025-01-01 00:00:00' AND customer_id <= 412

Frame metrics

No numeric metric is introduced in this frame.

Evidence set

derived-plan.jsonevidence:composite-misordered-derived-plan

derived

fixture-sets.jsonevidence:composite-misordered-fixture-sets

derived

native-plan.jsonevidence:composite-misordered-native-plan

observed

result.jsonevidence:composite-misordered-result

observed

runtime-counters.jsonevidence:composite-misordered-runtime-counters

observed

Captured · MySQL 8.4.10 plan, trace, counters, and result

Derived · fixture · exact predicate sets and cutoff tuples

Model · key roles, boundary tuple, ICP, and materialization flow

.composite-workbench-shell { display: none !important; } .noscript-explanation { display: block !important; }

Full explanation

Inspect composite-index column order, range boundaries, ICP, and covering lookup from real MySQL 8.4.10 plans and a 131,072-row fixture.

SQL

SELECT id, customer_id, status, ordered_at, total_cents
FROM orders
WHERE customer_id = 412
  AND status = 'PAID'
  AND ordered_at >= '2025-01-01 00:00:00'
ORDER BY ordered_at DESC, id DESC
LIMIT 20;

Misordered

Selected key: idx_orders_status_date_customer
Access type: range
used_key_parts: status, ordered_at

Read the SQL and index definition together

Trace customer_id=412, status='PAID', the ordered_at lower bound, descending order, and LIMIT 20 through one access path.

Inspect the range access MySQL selected

Read the selected key, access type, key length, estimated rows, filtered, and cost from the same native range-scan operator.

Only status forms the leftmost equality prefix

The leading status='PAID' column forms the equality prefix; customer_id=412 appears after the ordered_at range and does not extend it.

status and ordered_at form the base range

used_key_parts and the normal range alternative contain status and ordered_at. These parts define the base candidate span across the date range.

customer_id participates as a boundary suffix and in ICP

The low-LIMIT recheck includes customer_id<=412 as a suffix in the chosen tuple, while ICP evaluates customer_id=412 during the scan. id preserves result order.

The status/date range contains 31,348 rows

M_RANGE is the exact set of 31,348 fixture rows satisfying status='PAID' and the ordered_at lower bound, not an engine visit trace.

customer_id=412 leaves 31 rows

Of 31,348 candidates, 31 have customer_id=412 and 31,317 are rejected by the ICP predicate in the fixture relation.

total_cents requires the base row

The selected secondary index lacks total_cents, so the model shows the logical clustered-row lookup through the index entry's primary key.

Index order satisfies ORDER BY

ordered_at DESC, id DESC matches the selected index order, so the plan has no separate filesort operator.

Stop at the twentieth tuple

Order 83356 is the last returned row and order 79260 is the next excluded row. Compare their ordered_at and id values directly.

Return the captured twenty rows

Verify that all three variants return the same twenty rows and checksum f3b6a116…01a8b, including IDs, timestamps, and totals.

Reconcile plan, trace, counters, and result

Read used_key_parts and ranges from plan/trace, candidate relations from the fixture, and rows, cost, Handler counters, and result from their capture locators.

Captured result

MySQL 8.4.10 · 20 rows · f3b6a116a0d644412cd60167a96e4e8d628adbe75106f53d78c3893fef401a8b

Aligned

Selected key: idx_orders_customer_status_ordered_id
Access type: range
used_key_parts: customer_id, status, ordered_at

Read the SQL and index definition together

Trace customer_id=412, status='PAID', the ordered_at lower bound, descending order, and LIMIT 20 through one access path.

Inspect the range access MySQL selected

Read the selected key, access type, key length, estimated rows, filtered, and cost from the same native range-scan operator.

customer_id and status form the equality prefix

customer_id=412 and status='PAID' are consecutive leading equality conditions in the index.

ordered_at is the first boundary of a 31-row range

used_key_parts contains customer_id, status, and ordered_at; the ordered_at lower bound is the first range boundary after two equalities.

id remains an ordering suffix

id DESC does not further filter the range; it breaks ties for rows sharing the same ordered_at value.

The aligned prefix narrows the range to 31 rows

A_RANGE contains exactly 31 rows satisfying customer_id=412, status='PAID', and the ordered_at bound, equal to ELIGIBLE.

All 31 range candidates pass

customer_id and status are already in the range prefix, leaving no rejected fixture rows among the 31 candidates.

The range is narrow, but total_cents is still in the base row

This index still lacks total_cents, so projecting the 31 candidates requires the clustered-row materialization model.

Index order satisfies ORDER BY

ordered_at DESC, id DESC matches the selected index order, so the plan has no separate filesort operator.

Stop at the twentieth tuple

Order 83356 is the last returned row and order 79260 is the next excluded row. Compare their ordered_at and id values directly.

Return the captured twenty rows

Verify that all three variants return the same twenty rows and checksum f3b6a116…01a8b, including IDs, timestamps, and totals.

Reconcile plan, trace, counters, and result

Read used_key_parts and ranges from plan/trace, candidate relations from the fixture, and rows, cost, Handler counters, and result from their capture locators.

Captured result

MySQL 8.4.10 · 20 rows · f3b6a116a0d644412cd60167a96e4e8d628adbe75106f53d78c3893fef401a8b

Covering

Selected key: idx_orders_customer_status_ordered_id_total
Access type: range
used_key_parts: customer_id, status, ordered_at

Read the SQL and index definition together

Trace customer_id=412, status='PAID', the ordered_at lower bound, descending order, and LIMIT 20 through one access path.

Inspect the range access MySQL selected

Read the selected key, access type, key length, estimated rows, filtered, and cost from the same native range-scan operator.

customer_id and status form the same equality prefix

As in the noncovering variant, customer_id=412 and status='PAID' fix the leading equality prefix.

ordered_at forms the same 31-row range

Adding the covering suffix leaves used_key_parts and the first range boundary unchanged: customer_id, status, ordered_at.

id orders rows and total_cents completes projection

id DESC is the ordering suffix, while total_cents is the suffix that satisfies the SELECT projection inside the index entry.

Covering does not change the 31-row range

The total_cents suffix does not narrow the range, so A_RANGE remains the same 31 rows as in the noncovering variant.

All 31 rows remain result candidates

The leading equalities and range cover every WHERE predicate, producing 31 passes and zero rejects in the fixture.

The index entry supplies every selected value

With total_cents in the secondary index, all selected columns are projected without a base-row materialization model.

Index order satisfies ORDER BY

ordered_at DESC, id DESC matches the selected index order, so the plan has no separate filesort operator.

Stop at the twentieth tuple

Order 83356 is the last returned row and order 79260 is the next excluded row. Compare their ordered_at and id values directly.

Return the captured twenty rows

Verify that all three variants return the same twenty rows and checksum f3b6a116…01a8b, including IDs, timestamps, and totals.

Reconcile plan, trace, counters, and result

Read used_key_parts and ranges from plan/trace, candidate relations from the fixture, and rows, cost, Handler counters, and result from their capture locators.

Captured result

MySQL 8.4.10 · 20 rows · f3b6a116a0d644412cd60167a96e4e8d628adbe75106f53d78c3893fef401a8b