## Range Access Method for Single-Part Indexes 

•Equality Range Optimization of Many-Valued Comparisons 

•Skip Scan Range Access Method 

•Range Optimization of Row Constructor Expressions 

•Limiting Memory Use for Range Optimization 

For a single-part index, index value intervals can be conveniently represented by corresponding conditions in the WHERE  clause, denoted as range conditions  rather than “intervals.” 

The definition of a range condition for a single-part index is as follows: 

•For both BTREE  and HASH  indexes, comparison of a key part with a constant value is a range condition when using the = , <=> , IN() , IS NULL , or IS NOT NULL  operators. 

•Additionally, for BTREE  indexes, comparison of a key part with a constant value is a range condition when using the > , < , >= , <= , BETWEEN , != , or <>  operators, or LIKE  comparisons if the argument to LIKE  is a constant string that does not start with a wildcard character. 

•For all index types, multiple range conditions combined with OR  or AND  form a range condition. 

“Constant value”  in the preceding descriptions means one of the following: 

•A constant from the query string 

•A column of a const  or system  table from the same join 

•The result of an uncorrelated subquery 

•Any expression composed entirely from subexpressions of the preceding types 

Here are some examples of queries with range conditions in the WHERE  clause: 

SELECT * FROM t1 WHERE key_col  > 1 AND key_col  < 10; SELECT * FROM t1 WHERE key_col  = 1 OR key_col  IN (15,18,20); SELECT * FROM t1 WHERE key_col  LIKE ’ab%’ OR key_col  BETWEEN ’bar’ AND ’foo’; 

Some nonconstant values may be converted to constants during the optimizer constant propagation phase. 

MySQL tries to extract range conditions from the WHERE  clause for each of the possible indexes. During the extraction process, conditions that cannot be used for constructing the range condition are dropped, conditions that produce overlapping ranges are combined, and conditions that produce empty ranges are removed. 

Consider the following statement, where key1  is an indexed column and nonkey  is not indexed: 

SELECT * FROM t1 WHERE (key1 < ’abc’ AND (key1 LIKE ’abcde%’ OR key1 LIKE ’%b’)) OR (key1 < ’bar’ AND nonkey = 4) OR (key1 < ’uux’ AND key1 > ’z’); 

