Parameters (MariaDB 11.x Block Nested Loop)
customers row in the join buffer.Example query this animation executes
-- Non-indexed join executed by MariaDB 11.x BNL SELECT c.country, SUM(o.total) AS revenue FROM customers c JOIN orders o ON c.signup_month = o.signup_month GROUP BY c.country; -- no index on signup_month → BNL
BNL kicks in whenever the ON clause has no usable index on the inner side.
What you'll see in the animation
- Yellow blocks on top = chunks of customers rows packed into the join buffer (one block holds as many rows as join_buffer_size allows).
- Blue table below = the orders table. It is a full table — every row lives here.
- Small yellow circles flow from the active block along a curved path down to the orders table. They represent the outer rows being compared against the inner.
- A yellow sweep bar moves left-to-right across the orders table. That sweep is one full scan of orders.
- Each block triggers exactly one full scan of orders. 10 blocks = 10 full scans. That is why bigger join_buffer_size → fewer blocks → less work.
Cost readout (MariaDB 11.x BNL)
customers per block ?How many outer rows fit in one join_buffer_size chunk. Bigger buffer = more rows per block = fewer blocks = fewer inner rescans.
—
Blocks ?The outer table is split into this many blocks. Each block triggers one complete re-scan of the inner table.
—
Inner re-scans of orders ?The orders table is read from disk (or buffer pool) this many times — once per outer block. This is the main cost driver of BNL.
—
Row-pair comparisons ?Total number of (outer row, inner row) pairs compared. For BNL this is blocks × inner_rows × rows_per_block. Grows fast!
—
Complexity ?BNL re-scans orders once per block of customers. Doubling join_buffer_size halves the blocks and the rescans.
O(customers · orders / buffer) = O(n·m/b)
Row-pair comparisons vs customer rows (log–log, orders fixed)
Learn more — why does MariaDB still use BNL?
MariaDB controls block-based join algorithms with
join_cache_level (0–8), not optimizer_switch.
The default is 2 — "BNL without hashing". Levels 3
and 4 enable incremental and hashed BNL respectively.
MariaDB's "hashed BNL" (level 4) is not the same algorithm as MySQL 8.4's hash join. It's still BNL structurally — each outer block builds a tiny hash table, then the inner is scanned once per block and probed into that hash table. It's faster than plain BNL but still O(outer_blocks × inner_rows), not O(outer + inner). See the BNL vs hash lesson for the visual.
MySQL 8.0.20 removed BNL entirely — optimizer_switch=block_nested_loop
is a no-op in 8.4. For non-indexed equi-joins MySQL now always uses a
two-phase hash join.
Sources: MariaDB Knowledge Base "Block-based Join Algorithms"; "What's New in MySQL 8.0.20" release notes.