(mysql.info.gz) Index Merge optimization
Info Catalog
(mysql.info.gz) Range optimization
(mysql.info.gz) Query Speed
(mysql.info.gz) IS NULL optimization
7.2.6 Index Merge Optimization
------------------------------
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.
This "join" type optimization is new in MySQL 5.0.0, and represents a
significant change in behavior with regard to indexes, because the
_old_ rule was that the server is only ever able to use at most 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.
deficiencies:
* If a range scan is possible on some key, Index Merge will not be
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 will only consider the second plan. If that
not what you want, you can make the optimizer consider
`index_merge' by using `IGNORE INDEX' or `FORCE INDEX'. The
following queries will be 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.
Menu
* Index merge intersection Index Merge Intersection Access Algorithm
* Index merge union Index Merge Union Access Algorithm
* Index merge sort-union Index Merge Sort-Union Access Algorithm
Info Catalog
(mysql.info.gz) Range optimization
(mysql.info.gz) Query Speed
(mysql.info.gz) IS NULL optimization
automatically generated byinfo2html