## Index Merge Sort-Union Access Algorithm 

WHERE key1  = 1 OR key2  = 2 OR key3  = 3; SELECT * FROM innodb_table WHERE ( key1  = 1 AND key2  = 2) OR ( key3  = ’foo’ AND key4  = ’bar’) AND key5  = 5; 

This access algorithm is applicable when the WHERE  clause is converted to several range conditions combined by OR , but the Index Merge union algorithm is not applicable. 

Examples: 

SELECT * FROM tbl_name WHERE key_col1  < 10 OR key_col2  < 20; SELECT * FROM tbl_name WHERE ( key_col1  > 10 OR key_col2  = 20) AND nonkey_col  = 30; 

The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows. 

## Influencing Index Merge Optimization 

Use of Index Merge is subject to the value of the index_merge , index_merge_intersection , index_merge_union , and index_merge_sort_union  flags of the optimizer_switch  system variable. See Section 8.9.2, “Switchable Optimizations” . By default, all those flags are on . To enable only certain algorithms, set index_merge  to off , and enable only such of the others as should be permitted. 

In addition to using the optimizer_switch  system variable to control optimizer use of the Index Merge algorithms session-wide, MySQL supports optimizer hints to influence the optimizer on a per-statement basis. See Section 8.9.3, “Optimizer Hints” . 

## 8.2.1.4 Hash Join Optimization 

Beginning with MySQL 8.0.18, MySQL employs a hash join for any query for which each join has an equi- join condition, and in which there are no indexes that can be applied to any join conditions, such as this one: 

SELECT * FROM t1 JOIN t2 ON t1.c1=t2.c1; 

A hash join can also be used when there are one or more indexes that can be used for single-table predicates. 

A hash join is usually faster than and is intended to be used in such cases instead of the block nested loop algorithm (see Block Nested-Loop Join Algorithm) employed in previous versions of MySQL. Beginning with MySQL 8.0.20, support for block nested loop is removed, and the server employs a hash join wherever a block nested loop would have been used previously. 

In the example just shown and the remaining examples in this section, we assume that the three tables t1 , t2 , and t3  have been created using the following statements: 

CREATE TABLE t1 (c1 INT, c2 INT); CREATE TABLE t2 (c1 INT, c2 INT); CREATE TABLE t3 (c1 INT, c2 INT); 

You can see that a hash join is being employed by using EXPLAIN , like this: 

