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 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). 

The optimizer attempts to use additional key parts to determine the interval as long as the comparison operator is = , <=> , or IS NULL . If the operator is > , < , >= , <= , != , <> , BETWEEN , or LIKE , the optimizer uses it but considers no more key parts. For the following expression, the optimizer uses =  from the first comparison. It also uses >=  from the second comparison but considers no further key parts and does not use the third comparison for interval construction: 

key_part1  = ’foo’ AND key_part2  >= 10 AND key_part3  > 10 

The single interval is: 

(’foo’,10,-inf) < ( 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: 

