8.2.1.4 Engine Condition Pushdown Optimization
This optimization improves the efficiency of direct comparisons between a nonindexed column and a constant. In such cases, the condition is “pushed down” to the storage engine for evaluation. This optimization can be used only by the NDB
storage engine.
For NDB Cluster, this optimization can eliminate the need to send nonmatching rows over the network between the cluster's data nodes and the MySQL server that issued the query, and can speed up queries where it is used by a factor of 5 to 10 times over cases where condition pushdown could be but is not used.
Suppose that an NDB Cluster table is defined as follows:
CREATE TABLE t1 (
a INT,
b INT,
KEY(a)
) ENGINE=NDB;
Condition pushdown can be used with queries such as the one shown here, which includes a comparison between a nonindexed column and a constant:
SELECT a, b FROM t1 WHERE b = 10;
The use of condition pushdown can be seen in the output of EXPLAIN
:
mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using where with pushed condition
However, condition pushdown cannot be used with either of these two queries:
SELECT a,b FROM t1 WHERE a = 10;
SELECT a,b FROM t1 WHERE b + 1 = 10;
Condition pushdown is not applicable to the first query because an index exists on column a
. (An index access method would be more efficient and so would be chosen in preference to condition pushdown.) Condition pushdown cannot be employed for the second query because the comparison involving the nonindexed column b
is indirect. (However, condition pushdown could be applied if you were to reduce b + 1 = 10
to b = 9
in the WHERE
clause.)
Condition pushdown may also be employed when an indexed column is compared with a constant using a >
or <
operator:
mysql> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: range
possible_keys: a
key: a
key_len: 5
ref: NULL
rows: 2
Extra: Using where with pushed condition
Other supported comparisons for condition pushdown include the following:
column
[NOT] LIKEpattern
pattern
must be a string literal containing the pattern to be matched; for syntax, see Section 12.7.1, “String Comparison Functions and Operators”.column
IS [NOT] NULLcolumn
IN (value_list
)Each item in the
value_list
must be a constant, literal value.column
BETWEENconstant1
ANDconstant2
constant1
andconstant2
must each be a constant, literal value.
In all of the cases in the preceding list, it is possible for the condition to be converted into the form of one or more direct comparisons between a column and a constant.
Engine condition pushdown is enabled by default. To disable it at server startup, set the optimizer_switch
system variable. For example, in a my.cnf
file, use these lines:
[mysqld]
optimizer_switch=engine_condition_pushdown=off
At runtime, disable condition pushdown like this:
SET optimizer_switch='engine_condition_pushdown=off';
Limitations. Engine condition pushdown is subject to the following limitations:
Condition pushdown is supported only by the
NDB
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
orTEXT
types. This exclusion extends toJSON
,BIT
, andENUM
columns as well.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 extended
EXPLAIN
output to determine which conditions are actually pushed down. See Section 8.8.3, “Extended EXPLAIN Output Format”.