(mysql.info) range-access-multi-part
Info Catalog
(mysql.info) range-access-single-part
(mysql.info) range-optimization
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' defines this interval:
(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'' does not define a single
interval and cannot be used by the range access method.
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
range-access-single-part.
* 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
range-access-single-part, 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.
Info Catalog
(mysql.info) range-access-single-part
(mysql.info) range-optimization
automatically generated byinfo2html