(mysql.info.gz) Index Merge optimization

(mysql.info.gz) Range optimization
(mysql.info.gz) IS NULL optimization
 
 7.2.6 Index Merge Optimization
 ------------------------------
 
 The Index Merge (`index_merge') method is used to retrieve rows with
 several `ref', `ref_or_null', or `range' scans and merge the results
 into one.  This method is employed when the table condition is a
 disjunction of conditions for which `ref', `ref_or_null', or `range'
 could be used with different keys.
 
 This "join" type optimization is new in MySQL 5.0.0, and represents a
 significant change in behavior with regard to indexes, because the
 _old_ rule was that the server is only ever able to use at most one
 index for each referenced table.
 
 In `EXPLAIN' output, this 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.
 
 Examples:
 
      SELECT * FROM TBL_NAME WHERE KEY_PART1 = 10 OR KEY_PART2 = 20;
 
      SELECT * FROM TBL_NAME
          WHERE (KEY_PART1 = 10 OR KEY_PART2 = 20) AND NON_KEY_PART=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);
 
 The Index Merge method has several access algorithms (seen in the
 `Extra' field of `EXPLAIN' output):
 
    * intersection
 
    * union
 
    * sort-union
 
 The following sections describe these methods in greater detail.
 
 deficiencies:
 
    * If a range scan is possible on some key, Index Merge will not be
      considered.  For example, consider this query:
 
           SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
 
      For this query, two plans are possible:
 
        1. An Index Merge scan using the `(goodkey1 < 10 OR goodkey2 <
           20)' condition.
 
        2. A range scan using the `badkey < 30' condition.
 
      However, the optimizer will only consider the second plan. If that
      not what you want, you can make the optimizer consider
      `index_merge' by using `IGNORE INDEX' or `FORCE INDEX'. The
      following queries will be executed using Index Merge:
 
           SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2)
           WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
 
           SELECT * FROM t1 IGNORE INDEX(badkey)
           WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;
 
    * If your query has a complex `WHERE' clause with deep `AND'/`OR'
      nesting and MySQL doesn't choose the optimal plan, try
      distributing terms using the following identity laws:
 
           (X AND Y) OR Z = (X OR Z) AND (Y OR Z)
           (X OR Y) AND Z = (X AND Z) OR (Y AND Z)
 
 
 The choice between different possible variants of the `index_merge'
 access method and other access methods is based on cost estimates of
 various available options.
(mysql.info.gz) Index Merge optimization

Menu
