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.
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,
key_len contains a list of the longest
key parts for those indexes.
SELECT * FROM
key1= 10 OR
key2= 20; SELECT * FROM
key1= 10 OR
key2= 20) AND
non_key=30; SELECT * FROM t1, t2 WHERE (t1.
key1IN (1,2) OR t1.
value%') AND t2.
some_col; SELECT * FROM t1, t2 WHERE t1.
key1=1 AND (t2.
The Index Merge method has several access algorithms (seen in
Extra field of
The following sections describe these methods in greater detail.
The Index Merge optimization algorithm has the following known deficiencies:
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
However, the optimizer considers only the second plan.
z) AND (
z) OR (
Index Merge is not applicable to full-text 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.