| |
7.2.5.2. The Range Access Method for Multiple-Part Indexes
Range conditions on a multiple-part index are an extension of
range conditions for a single-part index. A range condition on
a multiple-part index restricts index rows to lie within one
or several key tuple intervals. Key tuple intervals are
defined over a set of key tuples, using ordering from the
index.
For example, consider a multiple-part index defined as
key1(key_part1,
key_part2,
key_part3), and the
following set of key tuples listed in key order:
key_part1 key_part2 key_part3
NULL 1 'abc'
NULL 1 'xyz'
NULL 2 'foo'
1 1 'abc'
1 1 'xyz'
1 2 'abc'
2 1 'aaa'
The condition key_part1 =
1
(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)
The interval covers the 4th, 5th, and 6th tuples in the
preceding data set and can be used by the range access method.
By contrast, the condition
key_part3 =
'abc'
The following descriptions indicate how range conditions work
for multiple-part indexes in greater detail.
-
For HASH indexes, each interval
containing identical values can be used. This means that
the interval can be produced only for conditions in the
following form:
key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
Here, const1,
const2, … are constants,
cmp is one of the
=, <=>, or
IS NULL comparison operators, and the
conditions cover all index parts. (That is, there are
N conditions, one for each part
of an N-part index.) For
example, the following is a range condition for a
three-part HASH index:
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
For the definition of what is considered to be a constant,
see Section 7.2.5.1, “The Range Access Method for Single-Part Indexes”.
-
For a BTREE index, an interval might be
usable for conditions combined with
AND, where each condition compares a
key part with a constant value using =,
<=>, IS NULL,
>, <,
>=, <=,
!=, <>,
BETWEEN, or LIKE
'pattern' (where
'pattern'
does not start with a wildcard). An interval can be used
as long as it is possible to determine a single key tuple
containing all rows that match the condition (or two
intervals if <> or
!= is used). For example, for this
condition:
key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
The single interval is:
('foo',10,10) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
It is possible that the created interval contains more
rows than the initial condition. For example, the
preceding interval includes the value ('foo', 11,
0), which does not satisfy the original
condition.
-
If conditions that cover sets of rows contained within
intervals are combined with OR, they
form a condition that covers a set of rows contained
within the union of their intervals. If the conditions are
combined with AND, they form a
condition that covers a set of rows contained within the
intersection of their intervals. For example, for this
condition on a two-part index:
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
The intervals are:
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
In this example, the interval on the first line uses one
key part for the left bound and two key parts for the
right bound. The interval on the second line uses only one
key part. The key_len column in the
EXPLAIN output indicates the maximum
length of the key prefix used.
In some cases, key_len may indicate
that a key part was used, but that might be not what you
would expect. Suppose that
key_part1 and
key_part2 can be
NULL. Then the
key_len column displays two key part
lengths for the following condition:
key_part1 >= 1 AND key_part2 < 2
But, in fact, the condition is converted to this:
key_part1 >= 1 AND key_part2 IS NOT NULL
Section 7.2.5.1, “The Range Access Method for Single-Part Indexes”, describes how
optimizations are performed to combine or eliminate intervals
for range conditions on a single-part index. Analogous steps
are performed for range conditions on multiple-part indexes.
|
|
