Movatterモバイル変換

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 theNDB 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;

Engine 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 engine condition pushdown can be seen in the output ofEXPLAIN:

mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 10        Extra: Using where with pushed condition

However, engine condition pushdowncannot 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;

Engine condition pushdown is not applicable to the first query because an index exists on columna. (An index access method would be more efficient and so would be chosen in preference to condition pushdown.) Engine condition pushdown cannot be employed for the second query because the comparison involving the nonindexed columnb is indirect. (However, engine condition pushdown could be applied if you were to reduceb + 1 = 10 tob = 9 in theWHERE clause.)

Engine 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: rangepossible_keys: a          key: a      key_len: 5          ref: NULL         rows: 2        Extra: Using where with pushed condition

Other supported comparisons for engine condition pushdown include the following:

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 theoptimizer_switch system variable'sengine_condition_pushdown flag tooff. For example, in amy.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: