Optimizing
SELECT
Statements
684
The use of triggered conditions has some performance implications. A
NULL IN (SELECT ...)
expression now may cause a full table scan (which is slow) when it previously did not. This is the price
paid for correct results (the goal of the trigger-condition strategy was to improve compliance and not
speed).
For multiple-table subqueries, execution of
NULL IN (SELECT ...)
will be particularly slow because
the join optimizer doesn't optimize for the case where the outer expression is
NULL
. It assumes that
subquery evaluations with
NULL
on the left side are very rare, even if there are statistics that indicate
otherwise. On the other hand, if the outer expression might be
NULL
but never actually is, there is no
performance penalty.
To help the query optimizer better execute your queries, use these tips:
• A column must be declared as
NOT NULL
if it really is. (This also helps other aspects of the
optimizer.)
• If you don't need to distinguish a
NULL
from
FALSE
subquery result, you can easily avoid the slow
execution path. Replace a comparison that looks like this:
outer_expr
IN (SELECT
inner_expr
FROM ...)
with this expression:
(
outer_expr
IS NOT NULL) AND (
outer_expr
IN (SELECT
inner_expr
FROM ...))
Then
NULL IN (SELECT ...)
will never be evaluated because MySQL stops evaluating
AND
[880]
parts as soon as the expression result is clear.
8.3.1.15.
LIMIT
Optimization
In some cases, MySQL handles a query differently when you are using
LIMIT row_count
and not
using
HAVING
:
• If you are selecting only a few rows with
LIMIT
, MySQL uses indexes in some cases when normally
it would prefer to do a full table scan.
• If you use
LIMIT row_count
with
ORDER BY
, MySQL ends the sorting as soon as it has found the
first
row_count
rows of the sorted result, rather than sorting the entire result. If ordering is done by
using an index, this is very fast. If a filesort must be done, all rows that match the query without the
LIMIT
clause must be selected, and most or all of them must be sorted, before it can be ascertained
that the first
row_count
rows have been found. In either case, after the initial rows have been
found, there is no need to sort any remainder of the result set, and MySQL does not do so.
• When combining
LIMIT row_count
with
DISTINCT
, MySQL stops as soon as it finds
row_count
unique rows.
• In some cases, a
GROUP BY
can be resolved by reading the key in order (or doing a sort on the key)
and then calculating summaries until the key value changes. In this case,
LIMIT row_count
does
not calculate any unnecessary
GROUP BY
values.
• As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you
are using
SQL_CALC_FOUND_ROWS
.
•
LIMIT 0
quickly returns an empty set. This can be useful for checking the validity of a query. When
using one of the MySQL APIs, it can also be employed for obtaining the types of the result columns.
(This trick does not work in the MySQL Monitor (the
mysql
program), which merely displays
Empty
set
in such cases; you should instead use
SHOW COLUMNS
or
DESCRIBE
for this purpose.)
• When the server uses temporary tables to resolve the query, it uses the
LIMIT row_count
clause
to calculate how much space is required.
8.3.1.16. How to Avoid Table Scans
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 ...
