Details
Minor
Description
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, ...)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Describing client case where it can be used
http://varokism.blogspot.fr/2013/06/mariadb-subquery-cache-in-real-use-case.html