content_image/709134.jpg
The extraction process for key key1  is as follows: 

1. Start with original WHERE  clause: 

(key1 < ’abc’ AND (key1 LIKE ’abcde%’ OR key1 LIKE ’%b’)) OR (key1 < ’bar’ AND nonkey = 4) OR (key1 < ’uux’ AND key1 > ’z’) 

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

3. Collapse conditions that are always true or false: 

• (key1 LIKE ’abcde%’ OR TRUE)  is always true 

• (key1 < ’uux’ AND key1 > ’z’)  is always false 

Replacing these conditions with constants yields: 

(key1 < ’abc’ AND TRUE) OR (key1 < ’bar’ AND TRUE) OR (FALSE) 

Removing unnecessary TRUE  and FALSE  constants yields: 

(key1 < ’abc’) OR (key1 < ’bar’) 

4. Combining overlapping intervals into one yields the final condition to be used for the range scan: 

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

<img src='content_image/709134.jpg'>

