Optimizing SELECT Statements 2 1 ’aaa’ The condition  key_part1  = 1  defines this interval: 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. 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: For the definition of what is considered to be a constant, see Range Access Method for Single-Part Indexes. 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: The single interval is: 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. The intervals are: •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: (1,-inf,-inf) <= ( key_part1 , key_part2 , key_part3 ) < (1,+inf,+inf) key_part1 cmp const1 AND  key_part2 cmp const2 AND ... AND  key_partN cmp constN ; key_part1  = 1 AND  key_part2  IS NULL AND  key_part3  = ’foo’ •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). •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  = ’foo’ AND  key_part2  >= 10 AND  key_part3  > 10 (’foo’,10,-inf) < ( key_part1 , key_part2 , key_part3 ) < (’foo’,+inf,+inf) ( key_part1  = 1 AND  key_part2  < 2) OR ( key_part1  > 5) 1646