Optimizing
SELECT
Statements
664
[mysqld]
engine_condition_pushdown=1
At runtime, enable condition pushdown with either of the following statements:
SET engine_condition_pushdown=ON;
SET engine_condition_pushdown=1;
Limitations.
Engine condition pushdown is subject to the following limitations:
• Condition pushdown is supported only by the
NDBCLUSTER
storage engine.
• Columns may be compared with constants only; however, this includes expressions which evaluate
to constant values.
• Columns used in comparisons cannot be of any of the
BLOB
or
TEXT
types.
• A string value to be compared with a column must use the same collation as the column.
• Joins are not directly supported; conditions involving multiple tables are pushed separately where
possible. Use
EXPLAIN EXTENDED
to determine which conditions are actually pushed down.
8.3.1.6.
IS NULL
Optimization
MySQL can perform the same optimization on
col_name
IS NULL
[877]
that it can use for
col_name
=
constant_value
. For example, MySQL can use indexes and ranges to search for
NULL
with
IS NULL
[877]
.
Examples:
SELECT * FROM
tbl_name
WHERE
key_col
IS NULL;
SELECT * FROM
tbl_name
WHERE
key_col
<=> NULL;
SELECT * FROM
tbl_name
WHERE
key_col
=
const1
OR
key_col
=
const2
OR
key_col
IS NULL;
If a
WHERE
clause includes a
col_name
IS NULL
[877]
condition for a column that is declared as
NOT NULL
, that expression is optimized away. This optimization does not occur in cases when the
column might produce
NULL
anyway; for example, if it comes from a table on the right side of a
LEFT
JOIN
.
MySQL can also optimize the combination
col_name = expr OR col_name IS NULL
, a form that
is common in resolved subqueries.
EXPLAIN
shows
ref_or_null
[647]
when this optimization is
used.
This optimization can handle one
IS NULL
[877]
for any key part.
Some examples of queries that are optimized, assuming that there is an index on columns
a
and
b
of
table
t2
:
SELECT * FROM t1 WHERE t1.a=
expr
OR t1.a IS NULL;
SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;
SELECT * FROM t1, t2
WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;
SELECT * FROM t1, t2
WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
Содержание 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 ...