Join Buffering and Block Nested Loop Joins in MySQL
When MySQL executes JOINs without usable indexes, it relies on join buffering and the Block Nested Loop (BNL) algorithm to reduce costly row-by-row comparisons. These techniques help MySQL process large joins more efficiently when index lookups are not possible.
Join buffering stores rows from the outer table in memory before probing the inner table.
It minimizes repeated full scans of the inner table by processing buffered batches of rows.
Enabled automatically when MySQL cannot use an index for the join condition.
When the JOIN condition does not use an index on the inner table.
When MySQL predicts that a nested-loop index join would be too slow.
When performing LEFT, RIGHT, or INNER JOINs requiring full table scans.
Join buffering appears in EXPLAIN as: Using join buffer (Block Nested Loop).
BNL is used when joining large tables without index support.
MySQL reads a batch of rows from the outer table into the join buffer.
For each batch, MySQL scans the entire inner table and performs comparisons.
This reduces the number of full inner-table scans from 'one per outer row' to 'one per outer batch'.
JOIN conditions like
ON t1.col = t2.colwheret2.colhas no index.Conditions involving functions or expressions (making index use impossible).
Joins on non-equality conditions (
<,>,<>,LIKE).
join_buffer_sizecontrols how large each buffer can be.Larger buffers reduce the number of passes over the inner table.
But setting it too high per-session can exhaust server RAM.
BNL is used when no index can be used for the join.
BKA (Batched Key Access) is used when an index exists but random access is expensive.
BNL scans the inner table; BKA performs grouped index lookups.
MySQL uses join buffering and block nested loop joins when the optimizer cannot perform indexed lookups. Join buffers batch rows from the outer table and reduce repeated table scans. BNL improves performance for non-indexed joins by scanning the inner table once per buffered batch instead of once per row. These mechanisms allow the optimizer to process large JOINs more efficiently even when indexes are not available.