## Index Merge Intersection Access Algorithm 

•Index Merge Intersection Access Algorithm 

•Index Merge Union Access Algorithm 

•Index Merge Sort-Union Access Algorithm 

•Influencing Index Merge Optimization 

This access algorithm is applicable when a WHERE  clause is converted to several range conditions on different keys combined with AND , and each condition is one of the following: 

•An N -part expression of this form, where the index has exactly N  parts (that is, all index parts are covered): 

key_part1  = const1  AND key_part2  = const2  ... AND key_partN  = constN 

•Any range condition over the primary key of an InnoDB  table. 

## Examples: 

SELECT * FROM innodb_table WHERE primary_key  < 10 AND key_col1  = 20; 

SELECT * FROM tbl_name WHERE key1_part1  = 1 AND key1_part2  = 2 AND key2  = 2; 

The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans. 

If all columns used in the query are covered by the used indexes, full table rows are not retrieved ( EXPLAIN  output contains Using index  in Extra  field in this case). Here is an example of such a query: 

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1; 

If the used indexes do not cover all columns used in the query, full rows are retrieved only when the range conditions for all used keys are satisfied. 

If one of the merged conditions is a condition over the primary key of an InnoDB  table, it is not used for row retrieval, but is used to filter out rows retrieved using other conditions. 

## Index Merge Union Access Algorithm 

The criteria for this algorithm are similar to those for the Index Merge intersection algorithm. The algorithm is applicable when the table’s WHERE  clause is converted to several range conditions on different keys combined with OR , and each condition is one of the following: 

•An N -part expression of this form, where the index has exactly N  parts (that is, all index parts are covered): 

key_part1  = const1  AND key_part2  = const2  ... AND key_partN  = constN 

•Any range condition over a primary key of an InnoDB  table. 

•A condition for which the Index Merge intersection algorithm is applicable. 

## Examples: 

SELECT * FROM t1 

