The Index Merge method is used to retrieve rows with several
rangescans 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.
EXPLAINoutput, the Index Merge method appears as
typecolumn. In this case, the
keycolumn contains a list of indexes used, and
key_lencontains 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 the
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 < 30condition.
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.