8.2.1.5 Engine Condition pushdown optimization engine optimization condition push

8.2.1.5 Engine Condition pushdown optimization engine optimization condition push

This optimization improves the efficiency of direct comparisons in a non indexed column and a constant, in which case the condition is pushed into the storage engine for evaluation,

This optimization can only be used by the NDB storage engine.

MySQL Cluster, the optimizer can eliminate the need to send mismatched records through the network in Cluster data nodes and MySQL Server execution query,

Suppose the MySQL Cluster table is defined as follows:

CREATE TABLE T1 (
A INT,
b INT,
KEY (a)
) Engine=ndb;

Conditional push can be used for queries, as shown in the figure above, which includes comparisons between non indexed columns and a constant
SELECT A, b from t1 WHERE B = 10;

The use of condition Pushdown can is 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 to used with either to these two queries:

However, conditional push cannot be used in the following 2 queries:

SELECT a,b from t1 WHERE a = 10;
SELECT a,b from t1 WHERE B + 1 = 10;

Conditional push is not applicable to the first query because one index has column A. (The access method for one index becomes more efficient, so a conditional push is selected)