Let's consider the query:
SELECT * FROM t1, t2 WHERE t1.a=t2.a AND t2.b BETWEEN t1.b1 AND t1.b2.
Let's assume that there is a compound index (a,b) on table t2.
If the cardinality of the table t2 is much bigger than the cardinality of the
table t1 then the optimizer, most probably, chooses an execution plan where rows
of t2 are accessed from rows of t1 by the ref access method that uses the index
(a,b) and sets the value only for the first component of the index.
With index condition pushdown disabled the evaluation of the predicate   
t2.b BETWEEN t1.b1 AND t2.b2 is done only after the data row matching join
condition t1.a=t2.a has been fetched.
With index condition pushdown this predicate is evaluated before the data row
has been fetched. It saves a random disk access for each joined row that does
not satisfies the condition t2.b BETWEEN t1.b1 AND t2.b2.
Currently, if the execution plan uses batched key access (BKA) to join rows of a
table, index condition pushdown for a predicate is never employed unless the
predicate contains no colums from the tables preceding the joined table. In our
case 'BETWEEN t1.b1 AND t2.b2' is such a predicate since rows of the table t1 is
accessed first. This is not an optimal behaviour for the BKA algorithm.

[A hint how to fix it: override the 'skip_record' callback function that is set
by the call of the multi_range_read_init method. This function has to filter out
the index entries yielded by index lookups that do not meet the pushdown
condition.] 

You must be logged in to tag this worklog

No Comments yet