content_image/690416.jpg
WHERE a=1 AND b=1 AND c=1 ... N ; 

•For a query with IN()  predicates: 

SELECT COUNT(*) FROM t WHERE a IN (1,2, ..., M ) AND b IN (1,2, ..., N ); 

Each literal value in an IN()  list counts as a predicate combined with OR . If there are two IN()  lists, the number of predicates combined with OR  is the product of the number of literal values in each list. Thus, the number of predicates combined with OR  in the preceding case is M  × N . 

## 8.2.1.3 Index Merge Optimization 

The Index Merge  access method retrieves rows with multiple range  scans and merges their results into one. This access method merges index scans from a single table only, not scans across multiple tables. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. 

Example queries for which Index Merge may be used: 

SELECT * FROM tbl_name  WHERE key1  = 10 OR key2  = 20; SELECT * FROM tbl_name WHERE ( key1  = 10 OR key2  = 20) AND non_key  = 30; SELECT * FROM t1, t2 WHERE (t1. key1  IN (1,2) OR t1. key2  LIKE ’ value %’) AND t2. key1  = t1. some_col ; SELECT * FROM t1, t2 WHERE t1. key1  = 1 AND (t2. key1  = t1. some_col  OR t2. key2  = t1. some_col2 ); 

<img src='content_image/690416.jpg'>

## Note 

The Index Merge optimization algorithm has the following known limitations: 

•If your query has a complex WHERE  clause with deep AND / OR  nesting and MySQL does not choose the optimal plan, try distributing terms using the following identity transformations: 

( x  AND y ) OR z  => ( x  OR z ) AND ( y  OR z ) ( x  OR y ) AND z  => ( x  AND z ) OR ( y  AND z ) 

•Index Merge is not applicable to full-text indexes. 

In EXPLAIN  output, the Index Merge method appears as index_merge  in the type  column. In this case, the key  column contains a list of indexes used, and key_len  contains a list of the longest key parts for those indexes. 

The Index Merge access method has several algorithms, which are displayed in the Extra  field of EXPLAIN  output: 

• Using intersect(...) 

• Using union(...) 

• Using sort_union(...) 

The following sections describe these algorithms in greater detail. The optimizer chooses between different possible Index Merge algorithms and other access methods based on cost estimates of the various available options. 

