Engine Atlas

Order Search Execution

Same SQL and data, different access paths
MySQL 8.4.102026-07-12
한국어
Learning guide

Question for this Lab

While returning the same 20 rows, where does the index reduce the actual read, filter, join, and sort work?

Execution engine: MySQL 8.4.10
Who runs it?

One session executes the same SELECT once with the baseline variant and once with the indexed variant.

What stays controlled?

Both variants use the same orders and customers fixture, predicates, ORDER BY, and LIMIT 20.

Executed SQL
SELECT
    o.id,
    o.ordered_at,
    o.total_cents,
    c.id AS customer_id,
    c.name AS customer_name
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.id
WHERE c.country_code = 'KR'
  AND o.status = 'PAID'
  AND o.ordered_at >= '2025-01-01 00:00:00'
ORDER BY o.ordered_at DESC, o.id DESC
LIMIT 20;
What changes?

The table or index access path, candidate rows read, join lookups, and rows entering the sort change.

What stays fixed?

The SQL meaning and the checksum of the 20 returned rows stay fixed across both variants.

Predict before tracing

What should you verify first after adding the index?

What should you verify first after adding the index?

Evidence mission

Open the indexed statement summary and confirm how statement work drops while the same 20 rows are returned.

Where do you look?
Read rows examined, sort rows, and the no-index-used flag together in the statement metrics of the Plan & Evidence panel.
Screen to open
Indexed · statement summary
What should you see?
Expected observation

The indexed statement records rows examined 4,040, sort rows 20, and no-index-used 0, against baseline 131,828, 31,348, and flag 1, while both variants keep result checksum efa5c8c2….

Evidence boundary
Captured
per-variant statement rows examined, sort rows, and no-index-used flag
Captured
the 20-row result checksum shared by both variants
Checkpoint · explain from evidenceWhat do baseline values 31,348 and 378 count?Reveal answer and basis

31,348 is the captured statement sort-row count. 378 is a fixture-derived prefix of sorted output consumed to produce LIMIT 20. They describe different stages and units.

Evidence boundary
Captured
31,348 statement sort rows
Derived · fixture
378-row prefix replay that fills LIMIT 20
Core takeaway

Judge the index by how it changes candidate, lookup, and sort paths needed to produce the same answer, not by changing the answer.

Next connection

The next Lab decomposes how composite-index column order changes range boundaries and base-row lookups.

Review terms
access pathIntroduced in Lab 01
The route chosen by the optimizer to find qualifying rows, such as a table scan or an index range scan.
filterIntroduced in Lab 01
The step that applies WHERE predicates to candidate rows and separates passing rows from rejected rows.
filesortIntroduced in Lab 01
MySQL's name for an explicit sort used when index order cannot satisfy ORDER BY; it does not necessarily mean a disk file.

01 · SOURCE

SQL

1 active fragmentMySQLLIMIT 20
SELECT o.id, o.ordered_at, o.total_cents, c.id AS customer_id, c.name AS customer_name
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE c.country_code = 'KR'
  AND o.status = 'PAID'
  AND o.ordered_at >= '2025-01-01 00:00:00'
ORDER BY o.ordered_at DESC, o.id DESC
LIMIT 20;
02 · FLOW

Execution Plan

01 / 13
00:00.0 / 00:15.6