One session executes the same SELECT in cold-page and warm-page capture variants.
From secondary index to clustered row
secondary index · clustered row · Buffer Poolmysql 8.4.10captured evidence
한국어Learning guide
Question for this Lab
In a noncovering secondary-index range, which entries are read and which clustered-row lookups follow from their carried primary keys?
Execution engine: mysql 8.4.10
The same secondary entries, selected primary keys, clustered rows, and result checksum are used.
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;
Captured page-state snapshots and counter deltas differ by variant, changing the emphasized pages.
The logical secondary-index to primary-key to clustered-row lookup and final result rows remain the same.
If the index does not contain total_cents, what is the next logical step?
Open the carried-primary-key step of the cold variant and check which primary key the secondary entry carries and why it is needed.
- Where do you look?
- Read the carried primary key in the index/row detail together with page state and counters in the Buffer Pool evidence.
- Screen to open
Cold · carried primary key
What should you see?
Expected observation
The explicit id key part of idx_orders_customer_status_ordered_id carries the primary key, and because total_cents is absent from the entry that key becomes the PRIMARY lookup key. Page state and counters are Captured; the secondary-entry to clustered-row path stays a Model.
Evidence boundary- Captured
- page-state snapshot and counter delta for the cold variant
- Model
- logical path from the carried primary key to the clustered row
Checkpoint · explain from evidenceCan the cold and warm views be described as a physical I/O order?Reveal answer and basis
No. The views show captured page-state snapshots and counter deltas. The secondary-entry to clustered-row path is a logical model, not a reconstruction of physical I/O order.
Evidence boundary- Captured
- page-state snapshots and counter deltas
- Model
- logical secondary-entry to clustered-row path
A noncovering index does not end at the secondary entry. Distinguish the carried primary key and base-row lookup without overstating page state as an I/O trace.
The next Lab follows the same clustered row into the row version visible to each transaction snapshot.
Review terms
base-row lookupIntroduced in Lab 06- The logical step that uses the carried primary key to find a clustered record for columns absent from a secondary index entry.
Buffer PoolIntroduced in Lab 06- The memory cache where InnoDB keeps and reuses index and data pages.
B-treeIntroduced in Lab 02- An index structure that stores ordered keys hierarchically to support equality and range lookups.
01 · SOURCE
Exact 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;- Limit
LIMIT 20 - range scan
secondary index
02 · FLOW
01 / 17Native plan and actual iterators
limitTREE line 1
-> Limit: 20 row(s) (cost=14.2 rows=20)
index-range-scanTREE line 2
-> Index range scan on orders using idx_orders_customer_status_ordered_id over (customer_id = 412 AND status = 'PAID' AND ordered_at <= '2025-01-01 00:00:00'), with index condition: ((orders.`status` = 'PAID') and (orders.customer_id = 412) and (orders.ordered_at >= TIMESTAMP'2025-01-01 00:00:00')) (cost=14.2 rows=31)
DATA PATH
ModelFrom index entry to clustered row
WHERE range
Derived · fixtureMatching secondary entries
| customer_id | status | ordered_at | id · primary key |
|---|---|---|---|
| 412 | PAID | 2026-06-24 23:20:00 | 130460 |
| 412 | PAID | 2026-06-17 20:40:00 | 129436 |
| 412 | PAID | 2026-05-20 10:00:00 | 125340 |
31 total · 28 entries omitted
primary key carried into clustered lookup130460
total_cents absent from secondary entryPRIMARY
Derived · fixtureExact clustered row
- id
- 130460
- customer_id
- 412
- status
- PAID
- ordered_at
- 2026-06-24 23:20:00
- total_cents
- 113740
ORDER BY and LIMIT 20 result
| id | customer_id | status | ordered_at | total_cents |
|---|---|---|---|---|
| 130460 | 412 | PAID | 2026-06-24 23:20:00 | 113740 |
| 129436 | 412 | PAID | 2026-06-17 20:40:00 | 4684 |
| 125340 | 412 | PAID | 2026-05-20 10:00:00 | 68460 |
| 124316 | 412 | PAID | 2026-05-13 07:20:00 | 209404 |
| 120220 | 412 | PAID | 2026-04-14 20:40:00 | 23180 |
| 119196 | 412 | PAID | 2026-04-07 18:00:00 | 164124 |
| 115100 | 412 | PAID | 2026-03-10 07:20:00 | 227900 |
| 114076 | 412 | PAID | 2026-03-03 04:40:00 | 118844 |
| 109980 | 412 | PAID | 2026-02-02 18:00:00 | 182620 |
| 108956 | 412 | PAID | 2026-01-26 15:20:00 | 73564 |
| 104860 | 412 | PAID | 2025-12-29 04:40:00 | 137340 |
| 103836 | 412 | PAID | 2025-12-22 02:00:00 | 28284 |
| 99740 | 412 | PAID | 2025-11-23 15:20:00 | 92060 |
| 98716 | 412 | PAID | 2025-11-16 12:40:00 | 233004 |
| 94620 | 412 | PAID | 2025-10-19 02:00:00 | 46780 |
| 93596 | 412 | PAID | 2025-10-11 23:20:00 | 187724 |
| 89500 | 412 | PAID | 2025-09-13 12:40:00 | 1500 |
| 88476 | 412 | PAID | 2025-09-06 10:00:00 | 142444 |
| 84380 | 412 | PAID | 2025-08-08 23:20:00 | 206220 |
| 83356 | 412 | PAID | 2025-08-01 20:40:00 | 97164 |
sha256 · f3b6a116a0d644412cd60167a96e4e8d628adbe75106f53d78c3893fef401a8b01 / 17