This optimization improves the efficiency of a direct comparison between a nonindexed column and a constant. In such cases, the condition is “pushed down” to the storage engine for evaluation. In MySQL 5.0, this optimization can be used only by the NDBCLUSTER storage engine, but we intend to implement it for additional storage engines in future versions of MySQL.

For MySQL 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 a MySQL Cluster table is defined as follows:

CREATE TABLE t1 (
    a INT,
    b INT,
    KEY(a)
) ENGINE=NDBCLUSTER;

Condition pushdown can be used with a query against this table such as the query shown here:

SELECT a,b FROM t1 WHERE b = 10;

This can be seen in the output of EXPLAIN SELECT:

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;

With regard to the first of these two queries, condition pushdown is not applicable 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.) In the case of the second query, condition pushdown cannot be employed 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 comparisons which are supported for 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.

Condition pushdown capability is not used by default. To enable it, you can start mysqld with the --engine-condition-pushdown option, or you can execute either of the following statements at runtime:

SET engine_condition_pushdown=ON;

SET engine_condition_pushdown=1;

Limitations.  Condition pushdown is subject to the following limitations: