Description:
Condition pushdown influences the way the server calls upon a particular storage engine to retrieve data. Currently, pushdown is enabled/disabled on the session level through the engine_condition_pushdown setting.
The session level is not fine grained enough for two reasons:
- one statement can include multiple conditions. In principle, each one of them could either be pushed down or evaluated afterwards. So, it would be nice to be able to somehow specify that a particular condition is to be pushed down
- one statement can include tables backed by multiple storage engines. Currently, it is only implemented for NDB, but as more engines implement it, one might need to specify exactly for which engines pushdown needs to be applied, as the characteristics for efficient pushdown may differ considerably per storage engine.
Fine grained control is especially necessary for good performance tuning. Currently engine_condition_pushdown can dramatically *slow down* particular queries on NDB tables, whereas it can dramatically speed up other queries. Consider this example:
SELECT *
FROM (
SELECT *
FROM table1
WHERE low_cardinality_attribute = const1
) t1
INNER JOIN table2 t2
ON t1.id = t2.t1_id
WHERE t2.high_cardinality_attribute = const2
Here, one would like to have the condition
t2.high_cardinality_attribute = const2
pushed down - pushdown will gain performance because we expect only very few rows as compared to the complete set.
For the other condition:
low_cardinality_attribute = const1
we expect maybe half of the rows being returned. In that case, pushdown can be very slow. In other cases (complex condition, OR etc) pushdown can slow down the query too. So what we really need is something like:
SELECT *
FROM (
SELECT *
FROM table1
WHERE /*! engine_condition_pushedown=OFF+*/ low_cardinality_attribute = const1
) t1
INNER JOIN table2 t2
ON t1.id = t2.t1_id
WHERE /*! engine_condition_pushedown=ON+*/t2.high_cardinality_attribute = const2
How to repeat:
na
Suggested fix:
na
Description: Condition pushdown influences the way the server calls upon a particular storage engine to retrieve data. Currently, pushdown is enabled/disabled on the session level through the engine_condition_pushdown setting. The session level is not fine grained enough for two reasons: - one statement can include multiple conditions. In principle, each one of them could either be pushed down or evaluated afterwards. So, it would be nice to be able to somehow specify that a particular condition is to be pushed down - one statement can include tables backed by multiple storage engines. Currently, it is only implemented for NDB, but as more engines implement it, one might need to specify exactly for which engines pushdown needs to be applied, as the characteristics for efficient pushdown may differ considerably per storage engine. Fine grained control is especially necessary for good performance tuning. Currently engine_condition_pushdown can dramatically *slow down* particular queries on NDB tables, whereas it can dramatically speed up other queries. Consider this example: SELECT * FROM ( SELECT * FROM table1 WHERE low_cardinality_attribute = const1 ) t1 INNER JOIN table2 t2 ON t1.id = t2.t1_id WHERE t2.high_cardinality_attribute = const2 Here, one would like to have the condition t2.high_cardinality_attribute = const2 pushed down - pushdown will gain performance because we expect only very few rows as compared to the complete set. For the other condition: low_cardinality_attribute = const1 we expect maybe half of the rows being returned. In that case, pushdown can be very slow. In other cases (complex condition, OR etc) pushdown can slow down the query too. So what we really need is something like: SELECT * FROM ( SELECT * FROM table1 WHERE /*! engine_condition_pushedown=OFF+*/ low_cardinality_attribute = const1 ) t1 INNER JOIN table2 t2 ON t1.id = t2.t1_id WHERE /*! engine_condition_pushedown=ON+*/t2.high_cardinality_attribute = const2 How to repeat: na Suggested fix: na