How does MySQL handle join buffering and block nested loop joins?

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.col where t2.col has no index.

Conditions involving functions or expressions (making index use impossible).

Joins on non-equality conditions (<, >, <>, LIKE).

join_buffer_size controls 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.