MySQL Join Algorithms

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

MySQL 8.4.10captured

01 · SOURCE

SQL shared by both variants

1 active fragmentsMySQLJOIN · 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