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, and
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 the
Extra field of
The following sections describe these methods in greater detail.
The Index Merge optimization algorithm has the following known deficiencies:
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.