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 ...)
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 ...
