Join shape controls
About this exampleAbout this example
Example query this animation executes
SELECT c.customer_id, o.order_id FROM customers c JOIN orders o ON o.customer_id = c.customer_id WHERE c.country = 'US';
This lesson isolates the Nested loop operator: one outer row becomes the driver, match pills arc from the outer row into the probe panel, then the next driver takes over. It's exactly what EXPLAIN's Nested loop node does at runtime.
What you'll see in the animation
- Left side = outer (driving) customers. Orange = currently driving the join.
- For each driver, <strong>match pills</strong> spawn at the outer row and arc into the probe panel — one per matching inner row. That arc IS the tuple flow — the point of the operator.
- Bottom-right verdict names the driver and its match count ('Acme id=1 → 2 orders ✓').
- Cost scales with outer_rows × inner_rows_per_probe. Indexed inner = tiny per-probe cost; un-indexed inner = full-scan per driver and cost explodes.
- Next step shows one outer row, then its probe; the simplified code beside the drawing highlights the lines each step runs.
Nested loop cost model
Outer rows ?Rows from the driving side of the join. The nested loop runs once per outer row.
—
Inner rows per probe ?Average rows checked on the inner side for each outer row.
—
Row-pair comparisons ?Approximate work for this operator: outer_rows × inner_rows_per_probe.
—
Complexity ?Nested loop cost grows multiplicatively with both inputs.
O(n · m)
Nested loop growth curve (log-log)
Learn more — when Nested loop is fast and when it isn't
The iterator itself is simple — one outer row, one inner probe, repeat. The difference between fast and catastrophic is what inner_rows_per_probe looks like:
- Indexed inner side (eq_ref / ref access): inner_rows_per_probe ≈ 1. Total work is ~linear in outer rows — this is the fast path and exactly what "add an index on the join column" achieves.
- Un-indexed inner side (type=ALL): the inner side is re-scanned per outer row, so inner_rows_per_probe = total inner rows. Work grows as outer × inner — the curve on the chart above is the pain.
MySQL 8.0.20+ rewrites this at execution time into a
hash join when no usable index exists (see
sql/sql_executor.cc:~2891
replace_with_hash_join). Which is why you'll see
plans labelled "Nested loop" that are actually running as hash
join under the hood — the EXPLAIN string is descriptive of the
optimizer's decision, not always the executor's behaviour.
Source: sql/iterators/composite_iterators.h:318
— NestedLoopIterator. "Currently the only form of
join we have" (at the logical level).