In MySQL, a Batched Key Access (BKA) Join algorithm is available that uses both index access to the joined table and a join buffer. The BKA algorithm supports inner join, outer join, and semijoin operations, including nested outer joins. Benefits of BKA include improved join performance due to more efficient table scanning. Also, the Block Nested-Loop (BNL) Join algorithm previously used only for inner joins is extended and can be employed for outer join and semijoin operations, including nested outer joins. 

The following sections discuss the join buffer management that underlies the extension of the original BNL algorithm, the extended BNL algorithm, and the BKA algorithm. For information about semijoin strategies, see Section 8.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations” 

•Join Buffer Management for Block Nested-Loop and Batched Key Access Algorithms 

•Block Nested-Loop Algorithm for Outer Joins and Semijoins 

•Batched Key Access Joins 

•Optimizer Hints for Block Nested-Loop and Batched Key Access Algorithms 

## Join Buffer Management for Block Nested-Loop and Batched Key Access Algorithms 

MySQL can employ join buffers to execute not only inner joins without index access to the inner table, but also outer joins and semijoins that appear after subquery flattening. Moreover, a join buffer can be effectively used when there is an index access to the inner table. 

The join buffer management code slightly more efficiently utilizes join buffer space when storing the values of the interesting row columns: No additional bytes are allocated in buffers for a row column if its value is NULL , and the minimum number of bytes is allocated for any value of the VARCHAR  type. 

The code supports two types of buffers, regular and incremental. Suppose that join buffer B1  is employed to join tables t1  and t2  and the result of this operation is joined with table t3  using join buffer B2 : 

•A regular join buffer contains columns from each join operand. If B2  is a regular join buffer, each row r put into B2  is composed of the columns of a row r1  from B1  and the interesting columns of a matching row r2  from table t3 . 

•An incremental join buffer contains only columns from rows of the table produced by the second join operand. That is, it is incremental to a row from the first operand buffer. If B2  is an incremental join buffer, it contains the interesting columns of the row r2  together with a link to the row r1  from B1 . 

Incremental join buffers are always incremental relative to a join buffer from an earlier join operation, so the buffer from the first join operation is always a regular buffer. In the example just given, the buffer B1  used to join tables t1  and t2  must be a regular buffer. 

Each row of the incremental buffer used for a join operation contains only the interesting columns of a row from the table to be joined. These columns are augmented with a reference to the interesting columns of the matched row from the table produced by the first join operand. Several rows in the incremental buffer can refer to the same row r  whose columns are stored in the previous join buffers insofar as all these rows match row r . 

Incremental buffers enable less frequent copying of columns from buffers used for previous join operations. This provides a savings in buffer space because in the general case a row produced by the first join operand can be matched by several rows produced by the second join operand. It is unnecessary to make several copies of a row from the first operand. Incremental buffers also provide a savings in processing time due to the reduction in copying time. 

In MySQL 8.0, the block_nested_loop  flag of the optimizer_switch  system variable works as follows: 

