Engine Atlas

MySQL Join Algorithms

Plan, row flow, and evidence for Indexed Nested Loop and Hash Join

MySQL 8.4.10captured

한국어

Learning guide

Question for this Lab

To produce the same join result, which input does nested-loop revisit and which inputs does hash join build and probe?

Execution engine: MySQL 8.4.10

Who runs it?

One session executes the same join SELECT in captured nested-loop and hash-join variants.

What stays controlled?

The same two input tables, SQL, returned rows, and checksum stay fixed, while only the indexed variant has idx_join_orders_customer_status in its capture condition.

Executed SQL

SELECT
  c.id AS customer_id,
  c.segment,
  o.id AS order_id,
  o.status,
  o.total_cents
FROM join_customers AS c
JOIN join_orders AS o
  ON o.customer_code = c.customer_code
WHERE c.region_code = 'KR'
  AND o.status = 'PAID';

What changes?

Index availability, operator structure, outer and inner repetition, hash build/probe stages, and related statement counters change.

What stays fixed?

The join predicate, matched result rows, and checksum stay identical across both algorithm variants.

Predict before tracing

Which two roles should you trace in the hash-join variant?

Sort input and LIMIT outputThe build input that creates the hash table and the probe input that finds matchesThe lock holder and waiting transaction

Evidence mission

Open the match-flow step of the hash-join variant and identify which input builds and which input probes.

Where do you look?: Read the build-side and probe-side markers in the join lens together with the Hash and Inner hash join operators in the plan.
Screen to open: Hash Join · match flow

What should you see?

Expected observation

The hash-join capture has a Hash operator as a direct child of the join and no index-lookup operator. idx_join_orders_customer_status exists only in the indexed-nested-loop capture condition, and both variants record 257 join output rows.

Evidence boundary

Captured: hash-join plan shape with its Hash child and Inner hash join operator, plus the 257 parent join output rows
Derived · fixture: JOIN_PAIRS membership matched on customer_code

Checkpoint · explain from evidenceCan the captured completion time from these two variants prove that one algorithm is generally superior?Reveal answer and basis

No. The capture is execution evidence for this fixture and server state. It explains algorithm structure and row flow but cannot establish a universal performance ranking.

Evidence boundary

Captured: plan shape, row flow, counters, and completion time

Core takeaway

Read a join comparison through the input that repeats, builds, or probes and through how the same result is assembled, not through the algorithm name alone.

Next connection

The next Lab follows intermediate rows from a join or aggregation into internal temporary-table and sort storage.

Review terms

nested-loop joinIntroduced in Lab 04: A join execution method that searches the inner input for matches for each row from the outer input.
hash build / probeIntroduced in Lab 04: The two phases that build a hash table from one join input and probe it with keys from the other input.

01 · SOURCE

SQL shared by both variants

1 active fragmentMySQLJOIN · no ORDER BY

SELECT c.id AS customer_id, c.segment, o.id AS order_id, o.status, o.total_cents
FROM join_customers AS c
JOIN join_orders AS o ON o.customer_code = c.customer_code
WHERE c.region_code = 'KR'
  AND o.status = 'PAID';

02 · FLOW

Native plan and actual iterators

01 / 12

STEP 01 · query shape

Read both filters and the join condition

The statement filters join_customers to KR and join_orders to PAID, then joins on customer_code. Both variants use these exact SQL bytes.

Active plan operators

hash-joinhash-join-join

-> Inner hash join (o.customer_code = c.customer_code) (cost=25825 rows=1341)line:1

Frame metrics

Join output rows: 257
Matched pair count: 257

Active evidence

fixture-join-pairsevidence:join-algorithms-hash-join-result-tsv

Derived · fixture

hash-join-explain-tree-txtevidence:join-algorithms-hash-join-explain-tree-txt

Captured

hash-join-normalized-operator-metricsevidence:join-algorithms-hash-join-explain-analyze-txt

Derived · capture

hash-join-normalized-planevidence:join-algorithms-hash-join-explain-json

Derived · capture

Capturednative TREE, ANALYZE, statement, and session evidence

Derived · fixtureexact rows, join membership, stable result order, and checksum

Modelactive key position used only for the animated explanation

.join-algorithms-page { overflow: auto !important; } .join-algorithms-page main { height: auto !important; min-height: 100dvh; } .join-workbench-shell { display: none !important; } .join-noscript { display: block !important; }

SQL & Capture Context · MySQL 8.4.10 · Captured

MySQL Join Algorithms - Indexed Nested Loop vs Hash Join

Compare MySQL 8.4.10 Hash Join with Indexed Nested Loop over identical SQL and fixture data through native plans, build/probe and outer/inner row flow, actual rows and loops, and exact matched pairs.

Query SHA-256: 11ffd828e9f211040949ec56dbdc117b3dfb3b29377d7208528a7647f8ec7558
Variants: 2
Frames: 12 per variant

SQL

SELECT
  c.id AS customer_id,
  c.segment,
  o.id AS order_id,
  o.status,
  o.total_cents
FROM join_customers AS c
JOIN join_orders AS o
  ON o.customer_code = c.customer_code
WHERE c.region_code = 'KR'
  AND o.status = 'PAID';

Two Execution Plans

Hash Join

Algorithm: hash-join
selectedKey: NULL
Captured join output: 257 rows
Captured join loops: 1

operator	access_type	cost	estimated rows	actual rows / loop	loops	native
`hash-join-join`	`not applicable`	25825	1341	—	1	`-> Inner hash join (o.customer_code = c.customer_code) (cost=25825 rows=1341)`line:1
`hash-join-order-filter`	`not applicable`	121	1310	2008	1	-> Filter: (o.`status` = 'PAID') (cost=121 rows=1310) (actual time=0.374..60.2 rows=2008 loops=1)line:2
`hash-join-order-scan`	`ALL`	121	130995	131072	1	`-> Table scan on o (cost=121 rows=130995) (actual time=0.365..47.7 rows=131072 loops=1)`line:3
`hash-join-hash`	`not applicable`	—	—	—	—	`-> Hash`line:4
`hash-join-customer-filter`	`not applicable`	104	102	126	1	`-> Filter: (c.region_code = 'KR') (cost=104 rows=102) (actual time=0.0629..0.47 rows=126 loops=1)`line:5
`hash-join-customer-scan`	`ALL`	104	1024	1024	1	`-> Table scan on c (cost=104 rows=1024) (actual time=0.0565..0.38 rows=1024 loops=1)`line:6

Indexed Nested Loop

Algorithm: indexed-nested-loop
selectedKey: idx_join_orders_customer_status
Captured join output: 257 rows
Captured join loops: 1

operator	access_type	cost	estimated rows	actual rows / loop	loops	native
`indexed-nested-loop-join`	`not applicable`	1282	3367	—	1	`-> Nested loop inner join (cost=1282 rows=3367)`line:1
`indexed-nested-loop-customer-filter`	`not applicable`	104	102	126	1	`-> Filter: (c.region_code = 'KR') (cost=104 rows=102) (actual time=0.0722..0.486 rows=126 loops=1)`line:2
`indexed-nested-loop-customer-scan`	`ALL`	104	1024	1024	1	`-> Table scan on c (cost=104 rows=1024) (actual time=0.0636..0.388 rows=1024 loops=1)`line:3
`indexed-nested-loop-order-lookup`	`ref`	8.25	32.9	2.04	126	`-> Index lookup on o using idx_join_orders_customer_status (customer_code=c.customer_code, status='PAID') (cost=8.25 rows=32.9) (actual time=0.0121..0.0126 rows=2.04 loops=126)`line:4

Operator & Row Flow

Hash Join

role	table	Actual rows / loop	Loops	Source
build	`join_customers`	126	1	Captured
probe	`join_orders`	2008	1	Captured

Captured join output: 257 rows Captured
Matched pairs: 257 pairs Derived · fixture

Indexed Nested Loop

role	table	Actual rows / loop	Loops	Source
outer	`join_customers`	126	1	Captured
inner	`join_orders`	2.04	126	Captured

Captured join output: 257 rows Captured
Matched pairs: 257 pairs Derived · fixture

Result Set Identity

257 total rows · 20 shown · 237 omitted

This Derived · fixture display is sorted by customer_id and order_id; it is not MySQL's native visit order.

SHA-256 edecce27bda10f840f1d54bda755b283368dd2703fa1bdbc3e6952dfa9a40048

257 total rows · 20 shown · 237 omitted
#	customer_id	segment	order_id	status	total_cents
1	5	SMB	83485	PAID	478418
2	23	PUBLIC	21263	PAID	418593
3	31	PUBLIC	62103	PAID	411640
4	31	PUBLIC	78487	PAID	38259
5	31	PUBLIC	104087	PAID	65809
6	31	PUBLIC	127639	PAID	58142
7	31	PUBLIC	130711	PAID	134844
8	37	SMB	12349	PAID	223286
9	37	SMB	17469	PAID	396245
10	37	SMB	39997	PAID	491616
11	37	SMB	66621	PAID	397863
12	39	ENTERPRISE	5663	PAID	113291
13	39	ENTERPRISE	12831	PAID	484591
14	39	ENTERPRISE	53791	PAID	64324
15	55	ENTERPRISE	31023	PAID	389822
16	55	ENTERPRISE	43311	PAID	10988
17	55	ENTERPRISE	78127	PAID	373331
18	60	STARTUP	17892	PAID	152430
19	60	STARTUP	79332	PAID	128047
20	83	PUBLIC	21387	PAID	180575

237 omitted · Derived · fixture

12-Step Execution Walkthrough

Hash Join

Read both filters and the join condition
The statement filters join_customers to KR and join_orders to PAID, then joins on customer_code. Both variants use these exact SQL bytes.
Open the captured Inner hash join shape
The native TREE contains an Inner hash join and a Hash child with no join-key secondary index. Build and probe roles come from this captured plan shape.
Read 1,024 customer rows on the build input
Table scan on c returned 1,024 rows in one loop in the capture. The stable row sample is selected from the fixed fixture.
Retain 126 KR customers on the build side
The c.region_code = 'KR' Filter recorded 126 actual rows per loop and one loop. Exact membership is reconciled with the fixture set.
Place customer_code on the hash build model
The displayed position for the selected customer_code is a Model. The active key shows logical movement without claiming an internal MySQL storage location.
Read 131,072 order rows on the probe input
Table scan on o returned 131,072 rows in one loop in the capture. Display samples are fixture rows selected stably by order ID.
Retain 2,008 PAID orders on the probe side
The o.status = 'PAID' Filter recorded 2,008 actual rows per loop and one loop. Exact PAID membership is computed from the fixture.
Connect pairs with the same probe customer_code
Visible pairs are a stable prefix of JOIN_PAIRS, and each membership is Derived · fixture. This display is not MySQL's native visit order.
Confirm 257 captured join output rows
The parent Inner hash join recorded 257 Join output rows. The Matched pair count of 257 is a separate Derived · fixture fact.
Display all 257 projected rows in stable order
The complete result sorted by customer_id and order_id, together with its checksum, is Derived · fixture. The display is not MySQL's native visit order.
Read statement and session deltas for one captured execution
This captured execution recorded join completion at 61.2 ms and 132,096 statement-level rows examined. Handler_read_rnd_next 132,098 is a session-level delta and is not assigned to an operator or row.
Reconcile native plans, normalized metrics, and fixture results
This frame reconciles the normalized plan structure read from TREE and ANALYZE with capture-normalized operator and statement metrics. Customers, orders, matched pairs, the complete sorted result rows, and the checksum are each checked against their Derived · fixture source.

Indexed Nested Loop

Read both filters and the join condition
The statement filters join_customers to KR and join_orders to PAID, then joins on customer_code. Query bytes and the result checksum match the Hash variant.
Open the captured Nested loop and selected index
The native TREE records a Nested loop inner join and an idx_join_orders_customer_status lookup. Outer and inner roles come from this captured plan shape.
Read 1,024 customer rows on the outer input
Table scan on c returned 1,024 rows in one loop in the capture. The stable row sample is selected from the fixed fixture.
Retain 126 KR customers on the outer side
The c.region_code = 'KR' Filter recorded 126 actual rows per loop and one loop. Those 126 rows match the captured inner lookup loop count.
Let one outer customer start an inner lookup
Movement of the selected customer_code is a Model. The active key marks a logical lookup position without asserting storage placement.
Find per-customer order candidates in the selected index
The inner side looks up idx_join_orders_customer_status by customer_code and PAID. The selected index name and lookup wording come from the native plan.
Read the captured inner average and loop count as recorded
The inner operator recorded Actual rows / loop 2.04 and Loops 126. Neither value is multiplied or rounded into another count.
Connect pairs that match the current outer key
Visible pairs are a stable prefix of JOIN_PAIRS, and each membership is Derived · fixture. This display is not MySQL's native visit order.
Confirm 257 captured parent output rows
The parent Nested loop recorded 257 Join output rows. The Matched pair count of 257 is a separate Derived · fixture fact and is not recomputed from 2.04 and 126.
Display all 257 projected rows in stable order
The complete result sorted by customer_id and order_id, together with its checksum, is Derived · fixture. The display is not MySQL's native visit order.
Read statement and session deltas for one captured execution
This captured execution recorded join completion at 2.1 ms and 1,281 statement-level rows examined. Handler_read_key 127 and Handler_read_next 257 are session-level deltas and are not assigned to an operator or row.
Reconcile native plans, normalized metrics, and fixture results
This frame reconciles the normalized plan structure read from TREE and ANALYZE with capture-normalized operator and statement metrics. Customers, orders, matched pairs, the complete sorted result rows, and the checksum are each checked against their Derived · fixture source.

Evidence & Source Boundaries

Captured: Values read from MySQL 8.4.10 native TREE, EXPLAIN ANALYZE, JSON plans, and statement or session counters
Derived · fixture: Exact membership, canonical results, and checksums produced by applying the same SQL predicates to the deterministic fixture
Model: A logical movement position for the active customer_code, without claiming an internal MySQL storage location

Hash Join · 12 frames · 13 published evidence records

Indexed Nested Loop · 12 frames · 13 published evidence records