7.2.6. 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.
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 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):
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 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:
However, the optimizer considers only 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)
Index Merge is not applicable to fulltext 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.
