Combined ref and range access

Currently, MariaDB (and MySQL) optimizer support:

• range access, which can use a wide set of conditions, but can only use comparison with constant (for example, tbl.key <'foo' is usable for range access, while "tbl.key < othertlbl.column" is not)

• ref access, which can use conditions that refer to other tables (like "t.key=othertbl.column") but is limited to equalities only.

This causes sub-optimal plans for queries like

SELECT * FROM t1, t2 WHERE t2.key1part1=t1.col AND t2.key1part2 BETWEEN $date1 and $date2

If the join order is t1, t2, table t2 can only be accessed using "t2.key1part1=t1.col". Or, one can have a separate index on t2.key1part2 and use the second part of WHERE. Both parts cannot be used.

There are optimizations that help handle these cases

• "Range checked for each record"
• Index Condition Pushdown

but they do not achieve top performance.

This task is about allowing to combine ref access with range. It is difficult to do in general, however, we can easily handle trivial cases like

ref_condition(t.keypart1...K)  AND range_condition(t.keypartK+1, ... )

In MariaDB 10.0, there is index statistics, so we will be able to get selectivity numbers for range_condition(t.keypartK+1, ...)