Skip navigation links
8.3.1.5 Engine Condition Pushdown Optimization »
Section Navigation      [Toggle]

8.3.1.4. Index Merge Optimization

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. This access method merges index scans from a single table; it does not merge scans across multiple tables.

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 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);

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.

Note

The Index Merge optimization algorithm has the following known deficiencies:

  • 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 full-text indexes. We plan to extend it to cover these in a future MySQL release.

  • If a range scan is possible on some key, the optimizer will not consider using Index Merge Union or Index Merge Sort-Union algorithms. 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.

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.

8.3.1.4.1. The Index Merge Intersection Access Algorithm

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

  • In 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.

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 don't 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 a primary key of an InnoDB table, it is not used for row retrieval, but is used to filter out rows retrieved using other conditions.

8.3.1.4.2. The Index Merge Union Access Algorithm

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

  • In 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 method intersection algorithm is applicable.

Examples: 

SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3;

SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR
  (key3='foo' AND key4='bar') AND key5=5;
8.3.1.4.3. The Index Merge Sort-Union Access Algorithm

This access algorithm is employed when the WHERE clause was converted to several range conditions combined by OR, but for which the Index Merge method union algorithm is not applicable.

Examples: 

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;

The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm must first fetch row IDs for all rows and sort them before returning any rows.

Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Ratheesh Bhat on March 1 2007 6:20am[Delete] [Edit]

Beware of having low selectivity indexes on your table. A complex AND/OR WHERE clause will surely make your query very very slow if Index_Merge optimization is being used with an intersect() algorithm.

Add your own comment.