For a single-part index, index value intervals can be
conveniently represented by corresponding conditions in the
WHERE
clause, so we speak of
range conditions rather than
“intervals.”
The definition of a range condition for a single-part index is as follows:
For both
BTREE
andHASH
indexes, comparison of a key part with a constant value is a range condition when using the=
,<=>
,IN()
,IS NULL
, orIS 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, orLIKE
comparisons if the argument toLIKE
is a constant string that does not start with a wildcard character.For all types of indexes, multiple range conditions combined with
OR
orAND
form a range condition.
“Constant value” in the preceding descriptions means one of the following:
Here are some examples of queries with range conditions in the
WHERE
clause:
SELECT * FROM t1 WHEREkey_col
> 1 ANDkey_col
< 10; SELECT * FROM t1 WHEREkey_col
= 1 ORkey_col
IN (15,18,20); SELECT * FROM t1 WHEREkey_col
LIKE 'ab%' ORkey_col
BETWEEN 'bar' AND 'foo';
Note that some nonconstant values may be converted to constants during the 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');
The extraction process for key key1
is as
follows:
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')
Remove
nonkey = 4
andkey1 LIKE '%b'
because they cannot be used for a range scan. The correct way to remove them is to replace them withTRUE
, so that we do not miss any matching rows when doing the range scan. Having replaced them withTRUE
, we get:(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR (key1 < 'bar' AND TRUE) OR (key1 < 'uux' AND key1 > 'z')
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, we get:
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
Removing unnecessary
TRUE
andFALSE
constants, we obtain:(key1 < 'abc') OR (key1 < 'bar')
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.
Currently, 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
.