Optimizing SELECT Statements 1645 The extraction process for key  key1  is as follows: Removing unnecessary  TRUE  and  FALSE  constants yields: Replacing these conditions with constants yields: (key1 < ’abc’ AND TRUE) OR (key1 < ’bar’ AND TRUE) OR (FALSE) (key1 < ’abc’) OR (key1 < ’bar’) (key1 < ’bar’) In general (and as demonstrated by the preceding example), the condition used for a range scan is less restrictive than the  WHERE  clause. MySQL performs an additional check to filter out rows that satisfy the range condition but not the full  WHERE  clause. The range condition extraction algorithm can handle nested  AND / OR  constructs of arbitrary depth, and its output does not depend on the order in which conditions appear in  WHERE  clause. MySQL does not support merging multiple ranges for the  range  access method for spatial indexes. To work around this limitation, you can use a  UNION  with identical  SELECT  statements, except that you put each spatial predicate in a different  SELECT . 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: Range Access Method for Multiple-Part Indexes 4. Combining overlapping intervals into one yields the final condition to be used for the range scan: • (key1 LIKE ’abcde%’ OR TRUE)  is always true • (key1 < ’uux’ AND key1 > ’z’)  is always false 3. Collapse conditions that are always true or false: 2. Remove  nonkey = 4  and  key1 LIKE ’%b’  because they cannot be used for a range scan. The correct way to remove them is to replace them with  TRUE , so that we do not miss any matching rows when doing the range scan. Replacing them with  TRUE  yields: (key1 < ’abc’ AND (key1 LIKE ’abcde%’ OR TRUE)) OR (key1 < ’bar’ AND TRUE) OR (key1 < ’uux’ AND key1 > ’z’) (key1 < ’abc’ AND (key1 LIKE ’abcde%’ OR key1 LIKE ’%b’)) OR (key1 < ’bar’ AND nonkey = 4) OR (key1 < ’uux’ AND key1 > ’z’) 1. Start with original  WHERE  clause: key_part1 key_part2 key_part3 NULL 1 ’abc’ NULL 1 ’xyz’ NULL 2 ’foo’ 1 1 ’abc’ 1 1 ’xyz’ 1 2 ’abc’