Optimizing
SELECT
Statements
675
the
WHERE
condition is not null-rejected for the embedded outer join, but the join condition of the
embedding outer join
T2.A=T1.A AND T3.C=T1.C
is null-rejected. So the query can be converted to:
SELECT * FROM T1 LEFT JOIN
(T2, T3)
ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B
WHERE T3.D > 0 OR T1.D > 0
The algorithm that converts outer join operations into inner joins was implemented in full measure, as it
has been described here, in MySQL 5.0.1. MySQL 4.1 performs only some simple conversions.
8.3.1.11.
ORDER BY
Optimization
In some cases, MySQL can use an index to satisfy an
ORDER BY
clause without doing any extra
sorting.
The index can also be used even if the
ORDER BY
does not match the index exactly, as long as all
of the unused portions of the index and all the extra
ORDER BY
columns are constants in the
WHERE
clause. The following queries use the index to resolve the
ORDER BY
part:
SELECT * FROM t1
ORDER BY
key_part1
,
key_part2
,... ;
SELECT * FROM t1
WHERE
key_part1
=
constant
ORDER BY
key_part2
;
SELECT * FROM t1
ORDER BY
key_part1
DESC,
key_part2
DESC;
SELECT * FROM t1
WHERE
key_part1
= 1
ORDER BY
key_part1
DESC,
key_part2
DESC;
SELECT * FROM t1
WHERE
key_part1
>
constant
ORDER BY
key_part1
ASC;
SELECT * FROM t1
WHERE
key_part1
<
constant
ORDER BY
key_part1
DESC;
SELECT * FROM t1
WHERE
key_part1
=
constant1
AND
key_part2
>
constant2
ORDER BY
key_part2
;
In some cases, MySQL cannot use indexes to resolve the
ORDER BY
, although it still uses indexes to
find the rows that match the
WHERE
clause. These cases include the following:
• You use
ORDER BY
on different keys:
SELECT * FROM t1 ORDER BY
key1
,
key2
;
• You use
ORDER BY
on nonconsecutive parts of a key:
SELECT * FROM t1 WHERE
key2
=
constant
ORDER BY
key_part2
;
• You mix
ASC
and
DESC
:
SELECT * FROM t1 ORDER BY
key_part1
DESC,
key_part2
ASC;
• The key used to fetch the rows is not the same as the one used in the
ORDER BY
:
SELECT * FROM t1 WHERE
key2
=
constant
ORDER BY
key1
;
• You use
ORDER BY
with an expression that includes terms other than the key column name:
SELECT * FROM t1 ORDER BY ABS(
key
);
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...