Optimizing
SELECT
Statements
682
ref: func
rows: 2
Extra: Using index
mysql>
SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: select (`test`.`t1`.`outer_expr`,
(((`test`.`t1`.`outer_expr`) in t2 on
maybe_null_key checking NULL))) AS `outer_expr IN (SELECT
maybe_null_key FROM t2)` from `test`.`t1`
The additional
OR ... IS NULL
condition makes query execution slightly more complicated (and
some optimizations within the subquery become inapplicable), but generally this is tolerable.
The situation is much worse when
outer_expr
can be
NULL
. According to the SQL interpretation of
NULL
as “unknown value,”
NULL IN (SELECT inner_expr ...)
should evaluate to:
•
NULL
, if the
SELECT
produces any rows
•
FALSE
, if the
SELECT
produces no rows
For proper evaluation, it is necessary to be able to check whether the
SELECT
has produced any rows
at all, so
outer_expr = inner_expr
cannot be pushed down into the subquery. This is a problem,
because many real world subqueries become very slow unless the equality can be pushed down.
Essentially, there must be different ways to execute the subquery depending on the value of
outer_expr
. In MySQL 5.0 before 5.0.36, the optimizer chose speed over distinguishing a
NULL
from
FALSE
result, so for some queries, you might get a
FALSE
result rather than
NULL
.
As of MySQL 5.0.36, the optimizer chooses SQL compliance over speed, so it accounts for the
possibility that
outer_expr
might be
NULL
.
If
outer_expr
is
NULL
, to evaluate the following expression, it is necessary to run the
SELECT
to
determine whether it produces any rows:
NULL IN (SELECT
inner_expr
FROM ... WHERE
subquery_where
)
It is necessary to run the original
SELECT
here, without any pushed-down equalities of the kind
mentioned earlier.
On the other hand, when
outer_expr
is not
NULL
, it is absolutely essential that this comparison:
outer_expr
IN (SELECT
inner_expr
FROM ... WHERE
subquery_where
)
be converted to this expression that uses a pushed-down condition:
EXISTS (SELECT 1 FROM ... WHERE
subquery_where
AND
outer_expr
=
inner_expr
)
Without this conversion, subqueries will be slow. To solve the dilemma of whether to push down or not
push down conditions into the subquery, the conditions are wrapped in “trigger” functions. Thus, an
expression of the following form:
outer_expr
IN (SELECT
inner_expr
FROM ... WHERE
subquery_where
)
is converted into:
EXISTS (SELECT 1 FROM ... WHERE
subquery_where
AND trigcond(
outer_expr
=
inner_expr
))
More generally, if the subquery comparison is based on several pairs of outer and inner expressions,
the conversion takes this comparison:
(
oe_1
, ...,
oe_N
) IN (SELECT
ie_1
, ...,
ie_N
FROM ... WHERE
subquery_where
)
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 ...
