On 02/17/2012 08:00 AM, Sergei Petrunia wrote:
Hi Timour,
So, we're trying to fix this by making group-by-loose-scan access method be able to scan forwards (when evaluating MIN) and/or backwards (if evaluating MAX) until it finds a record that satisfies the WHERE condition.
We still need to determine whether we need to do the scan or index jumps are sufficient.
== Task definition ==
We're targeting cases where
- the WHERE condition has references to MIN/MAX column - the range optimizer has constructed a SEL_ARG graph that refers to the MIN/MAX column.
- the index is defined as (ignoring the bound columns):
INDEX( group_by_columns, min_max_column)
and the range optimizer has constructed a SEL_TREE in form:
range_tree(group_by_columns) ---> range_tree1(min_max_column) | | | \---------> range_tree2(min_max_column) | | ... .... | \-------------------> range_treeN(min_max_column)
(the edges in the picture a SEL_ARG::next_key_part edges)
When the query executed, loose scan walks the index forward: - it gets to some value group of {group_by_columns} - Within that group, we jump to the first (or to the last) index record that matches SEL_ARG(min_max).
The question:
when can we guarantee that the first index record will match the WHERE condition (PROB1)
?
== Proposed resolution ==
(PROB1) is true, when the WHERE condition is equivalent to
"cond1 AND cond2"
where cond1 - does not use min_max_column at all. cond2 - is an AND/OR tree with leaves in form "min_max_column $CMP$ const".
BETWEEN, IN, IS [NOT] NULL predicates are also ok here. Hidden type conversions should be taken into account. Regards, Igor.
I think the above is a sound solution. Please let me know if it is not. BR Sergei