Optimizing SELECT Statements Prior to MySQL 8.0.18, this algorithm was applied for equi-joins when no indexes could be used; in MySQL 8.0.18 and later, the hash join optimization is employed in such cases. Starting with MySQL 8.0.20, the block nested loop is no longer used by MySQL, and a hash join is employed for in all cases where the block nested loop was used previously. See Section 8.2.1.4, “Hash Join Optimization” . MySQL join buffering has these characteristics: •Join buffering can be used when the join is of type  ALL  or  index  (in other words, when no possible keys can be used, and a full scan is done, of either the data or index rows, respectively), or  range . Use of buffering is also applicable to outer joins, as described in Section 8.2.1.12, “Block Nested-Loop and Batched Key Access Joins” . •A join buffer is never allocated for the first nonconstant table, even if it would be of type  ALL  or  index . •Only columns of interest to a join are stored in its join buffer, not whole rows. •The  join_buffer_size  system variable determines the size of each join buffer used to process a query. •One buffer is allocated for each join that can be buffered, so a given query might be processed using multiple join buffers. •A join buffer is allocated prior to executing the join and freed after the query is done. For the example join described previously for the NLJ algorithm (without buffering), the join is done as follows using join buffering: for each row in t1 matching range { for each row in t2 matching reference key { store used columns from t1, t2 in join buffer if buffer is full { for each row in t3 { for each t1, t2 combination in join buffer { if row satisfies join conditions, send to client } } empty join buffer } } } if buffer is not empty { for each row in t3 { for each t1, t2 combination in join buffer { if row satisfies join conditions, send to client } } } If  S  is the size of each stored  t1 ,  t2  combination in the join buffer and  C  is the number of combinations in the buffer, the number of times table  t3  is scanned is: ( S  *  C )/join_buffer_size + 1 The number of  t3  scans decreases as the value of  join_buffer_size  increases, up to the point when join_buffer_size  is large enough to hold all previous row combinations. At that point, no speed is gained by making it larger. 8.2.1.8 Nested Join Optimization The syntax for expressing joins permits nested joins. The following discussion refers to the join syntax described in Section 13.2.10.2, “JOIN Clause” . 1661