maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hi,

So, MySQL 5.7 has got support for range optimization for multi-column IN
predicates:

http://optimize-this.blogspot.ru/2013/12/range-access-now-in-in-predicate-near.html

We've also got a user issue about it:
https://mariadb.atlassian.net/browse/MDEV-4903.

I'm looking at their IN handling. I don't see anything apparently wrong with
it.

They don't process multi-column NOT IN, but one can say that they just have a
limited scope.

Relevant code in opt_range.cc for single-column IN:


  case Item_func::IN_FUNC:
  {
    Item_func_in *func=(Item_func_in*) cond_func;

    if (inv)
    {
       // complicated code to handle NOT IN
    }
    else
    {    
      tree= get_mm_parts(param, cond_func, field, Item_func::EQ_FUNC,
                         func->arguments()[1], cmp_type);
      if (tree)
      {
        Item **arg, **end;
        for (arg= func->arguments()+2, end= arg+func->argument_count()-2;
             arg < end ; arg++)
        {
          tree= tree_or(param, tree, get_mm_parts(param, cond_func, field, 
                                                  Item_func::EQ_FUNC,
                                                  *arg, cmp_type));
        }
      }
    }


mysql-5.7 extends this approach to handle multi-column case (by invoking
get_mm_parts() for each compared value, then doing tree_and(), and then
tree_or()).

Should we just go ahead and backport it?


BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog