7.2.6.1. The Index Merge Intersection Access Algorithm
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 AND key_part2=const2 ... AND key_partN=constN
Any range condition over a primary key of an
InnoDB or BDB table.
Examples:
SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;
SELECT * FROM tbl_name
WHERE (key1_part1=1 AND key1_part2=2) AND key2=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 or BDB
table, it is not used for row retrieval, but is used to filter
out rows retrieved using other conditions.
