Optimizing
SELECT
Statements
657
SELECT ... FROM
tbl_name
ORDER BY
key_part1
,
key_part2
,... ;
SELECT ... FROM
tbl_name
ORDER BY
key_part1
DESC,
key_part2
DESC, ... ;
8.3.1.3. Range Optimization
The
range
[647]
access method uses a single index to retrieve a subset of table rows that are
contained within one or several index value intervals. It can be used for a single-part or multiple-part
index. The following sections give a detailed description of how intervals are extracted from the
WHERE
clause.
8.3.1.3.1. The Range Access Method for Single-Part Indexes
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
and
HASH
indexes, comparison of a key part with a constant value is a range
condition when using the
=
[875]
,
<=>
[876]
,
IN()
[878]
,
IS NULL
[877]
, or
IS NOT
NULL
[877]
operators.
• Additionally, for
BTREE
indexes, comparison of a key part with a constant value is a range condition
when using the
>
[876]
,
<
[876]
,
>=
[876]
,
<=
[876]
,
BETWEEN
[877]
,
!=
[876]
, or
<>
[876]
operators, or
LIKE
[896]
comparisons if the argument to
LIKE
[896]
is a constant
string that does not start with a wildcard character.
• For all types of indexes, multiple range conditions combined with
OR
[881]
or
AND
[880]
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
[646]
or
system
[646]
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';
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.
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 ...
