Optimizing
SELECT
Statements
658
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:
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. Having replaced them with
TRUE
, we get:
(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, we get:
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
Removing unnecessary
TRUE
and
FALSE
constants, we obtain:
(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
[880]
/
OR
[881]
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
[647]
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
.
8.3.1.3.2. The 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:
key_part1
key_part2
key_part3
NULL 1 'abc'
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
