Skip navigation links
7.2.6.1 Index Merge Intersection Access Algorithm »
Section Navigation      [Toggle]

7.2.6. Index Merge Optimization

[+/-]

7.2.6.1. Index Merge Intersection Access Algorithm
7.2.6.2. Algoritmo de acceso a Index Merge Union Access Algorithm
7.2.6.3. Index Merge Sort-Union Access Algorithm

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.

Note: 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, 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.

Note: The Index Merge optimization algorithm has the following known 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:

    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 only considers 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)

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.

Ésta es una traducción del manual de referencia de MySQL, que puede encontrarse en dev.mysql.com. El manual de referencia original de MySQL está escrito en inglés, y esta traducción no necesariamente está tan actualizada como la versión original. Para cualquier sugerencia sobre la traducción y para señalar errores de cualquier tipo, no dude en dirigirse a mysql-es@vespito.com.