(mysql.info) index-merge-optimization

 
 7.2.6 Index Merge Optimization
 ------------------------------

 
* index-merge-intersection     The Index Merge Intersection Access Algorithm
* index-merge-union            The Index Merge Union Access Algorithm
* index-merge-sort-union       The Index Merge Sort-Union Access Algorithm
 
 The Index Merge method is used to retrieve rows with several `range'
 scans and to merge their results into one. The merge can produce unions,
 intersections, or unions-of-intersections of its underlying scans.
 
 * If you have upgraded from a previous version of MySQL, you
 should be aware that this type of join optimization is first introduced
 in MySQL 5.0, and represents a significant change in behavior with
 regard to indexes. (Formerly, MySQL was able to use at most only one
 index for each referenced table.)
 
 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.
 
 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):
 
    * `Using intersect(...)'
 
    * `Using union(...)'
 
    * `Using sort_union(...)'
 
 The following sections describe these methods in greater detail.
 
 deficiencies:
 
    * If a range scan is possible on some key, an Index Merge is not
      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:
 
         * An Index Merge scan using the `(goodkey1 < 10 OR goodkey2 <
           20)' condition.
 
         * A range scan using the `badkey < 30' condition.
 
      However, the optimizer considers only the second plan. If that is
      not what you want, you can make the optimizer consider Index Merge
      by using `IGNORE INDEX' or `FORCE INDEX'. The following queries
      are 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)
 
    * Index Merge is not applicable to fulltext indexes. We plan to
      extend it to cover these in a future MySQL release.
 
 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.

Info Catalog

(mysql.info) range-optimization

(mysql.info) query-speed

(mysql.info) is-null-optimization

automatically generated byinfo2html

(mysql.info) index-merge-optimization

Menu