Bug #28371 Add SQL 'hints' for fine grained control of engine_condition_pushdown
Submitted: 11 May 2007 8:43 Modified: 31 May 2007 5:59
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.1.16 OS:Any
Assigned to: CPU Architecture:Any

[11 May 2007 8:43] Roland Bouman
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
[31 May 2007 5:59] Valeriy Kravchuk
Thank you for a reasonable feature request.