maria-developers team mailing list archive

[Sergei Petrunia <psergey@xxxxxxxxxxxx>]

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".


I think the above is a sound solution. Please let me know if it is not.
BR
 Sergei
-- 
Sergei Petrunia, Software Developer
Monty Program AB, http://askmonty.org
Blog: http://s.petrunia.net/blog