maria-developers team mailing list archive

[Igor Babaev <igor@xxxxxxxxxxxx>]

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