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(`

, and the
following set of key tuples listed in key order:
* key_part1*,

*,*

`key_part2`

*)*

`key_part3`

`key_part1`

`key_part2`

NULL 1 'abc' NULL 1 'xyz' NULL 2 'foo' 1 1 'abc' 1 1 'xyz' 1 2 'abc' 2 1 'aaa'`key_part3`

The condition

defines this interval:
* key_part1* =
1

(1,-inf,-inf) <= (,`key_part1`

,`key_part2`

) < (1,+inf,+inf)`key_part3`

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

does not define a single interval and cannot
be used by the range access method.
* 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`

AND`const1`

`key_part2`

`cmp`

AND ... AND`const2`

`key_partN`

`cmp`

;`constN`

Here,

,`const1`

, … are constants,`const2`

is one of the`cmp`

`=`

,`<=>`

, or`IS NULL`

comparison operators, and the conditions cover all index parts. (That is, there areconditions, one for each part of an`N`

-part index.) For example, the following is a range condition for a three-part`N`

`HASH`

index:= 1 AND`key_part1`

IS NULL AND`key_part2`

= 'foo'`key_part3`

For the definition of what is considered to be a constant, see Section 7.13.1.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 '`

(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'`pattern`

`<>`

or`!=`

is used). For example, for this condition:= 'foo' AND`key_part1`

>= 10 AND`key_part2`

> 10`key_part3`

The single interval is:

('foo',10,10) < (

,`key_part1`

,`key_part2`

) < ('foo',+inf,+inf)`key_part3`

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:(

= 1 AND`key_part1`

< 2) OR (`key_part2`

> 5)`key_part1`

The intervals are:

(1,-inf) < (

,`key_part1`

) < (1,2) (5,-inf) < (`key_part2`

,`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 thatand`key_part1`

can be`key_part2`

`NULL`

. Then the`key_len`

column displays two key part lengths for the following condition:>= 1 AND`key_part1`

< 2`key_part2`

But, in fact, the condition is converted to this:

>= 1 AND`key_part1`

IS NOT NULL`key_part2`

Section 7.13.1.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.