Optimizing SELECT Statements WHERE a=1 AND b=1 AND c=1 ...  N ; SELECT COUNT(*) FROM t WHERE a IN (1,2, ...,  M ) AND b IN (1,2, ...,  N ); 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 ); •For a query with  IN()  predicates: 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 . 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: The Index Merge optimization algorithm has the following known limitations: ( x  AND  y ) OR  z  => ( x  OR  z ) AND ( y  OR  z ) ( x  OR  y ) AND  z  => ( x  AND  z ) OR ( y  AND  z ) 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: 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. • Using intersect(...) • Using union(...) • Using sort_union(...) •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: •Index Merge is not applicable to full-text indexes. 1651 Note 8.2.1.3 Index Merge Optimization