This access algorithm can be employed when a
WHERE
clause was converted to several range
conditions on different keys combined with
AND
, and each condition is one of
the following:
In this form, where the index has exactly
N
parts (that is, all index
parts are covered):
key_part1
=const1
ANDkey_part2
=const2
... ANDkey_partN
=constN
Any range condition over a primary key of an
InnoDB
table.
Examples:
SELECT * FROMinnodb_table
WHEREprimary_key
< 10 ANDkey_col1
=20; SELECT * FROMtbl_name
WHERE (key1_part1
=1 ANDkey1_part2
=2) ANDkey2
=2;
The Index Merge intersection algorithm performs simultaneous scans on all used indexes and produces the intersection of row sequences that it receives from the merged index scans.
If all columns used in the query are covered by the used
indexes, full table rows are not retrieved
(EXPLAIN
output contains
Using index
in Extra
field in this case). Here is an example of such a query:
SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1;
If the used indexes don't cover all columns used in the query, full rows are retrieved only when the range conditions for all used keys are satisfied.
If one of the merged conditions is a condition over a primary
key of an InnoDB
table, it is not used for
row retrieval, but is used to filter out rows retrieved using
other conditions.
User Comments
Add your own comment.