[Commits] Rev 2879: MWL#89 in file:///home/tsk/mprog/src/5.3-mwl89/

timour at askmonty.org timour at askmonty.org
Tue Jan 18 00:53:49 EET 2011


At file:///home/tsk/mprog/src/5.3-mwl89/

------------------------------------------------------------
revno: 2879
revision-id: timour at askmonty.org-20110117225341-0mm693mic2thb25o
parent: timour at askmonty.org-20110117114814-wuu2o3o9a0gkyl8l
committer: timour at askmonty.org
branch nick: 5.3-mwl89
timestamp: Tue 2011-01-18 00:53:41 +0200
message:
  MWL#89
  
  Fixed query plans with loose index scan degraded into index scan.
  
  Analysis:
  With MWL#89 subqueries are no longer executed and substituted during
  the optimization of the outer query. As a result subquery predicates
  that were previously executed and substituted by a constant before
  the range optimizer were present as regular subquery predicates during
  range optimization. The procedure check_group_min_max_predicates()
  had a naive test that ruled out all queries with subqueries in the
  WHERE clause. This resulted in worse plans with MWL#89.
  
  Solution:
  The solution is to refine the test in check_group_min_max_predicates()
  to check if each MIN/MAX argument is referred to by a subquery predicate.
-------------- next part --------------
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2010-10-30 13:07:45 +0000
+++ b/mysql-test/r/group_min_max.result	2011-01-17 22:53:41 +0000
@@ -1360,12 +1360,158 @@ group by a1,a2,b;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1      index   NULL    idx_t1_1        163     NULL    128     Using where; Using index
 2       DEPENDENT SUBQUERY      t2      index   NULL    idx_t2_1        163     NULL    164     Using where; Using index
+select a1,a2,b,min(c),max(c) from t1
+where exists ( select * from t2 where t2.c = t1.c )
+group by a1,a2,b;
+a1      a2      b       min(c)  max(c)
+a       a       a       a111    d111
+a       a       b       e112    h112
+a       b       a       i121    l121
+a       b       b       m122    p122
+b       a       a       a211    d211
+b       a       b       e212    h212
+b       b       a       i221    l221
+b       b       b       m222    p222
+c       a       a       a311    d311
+c       a       b       e312    h312
+c       b       a       i321    l321
+c       b       b       m322    p322
+d       a       a       a411    d411
+d       a       b       e412    h412
+d       b       a       i421    l421
+d       b       b       m422    p422
 explain select a1,a2,b,min(c),max(c) from t1
 where exists ( select * from t2 where t2.c > 'b1' )
 group by a1,a2,b;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY t1      range   NULL    idx_t1_1        147     NULL    17      Using index for group-by
+1       PRIMARY t1      range   NULL    idx_t1_1        147     NULL    17      Using where; Using index for group-by
 2       SUBQUERY        t2      index   NULL    idx_t2_1        163     NULL    164     Using where; Using index
+select a1,a2,b,min(c),max(c) from t1
+where exists ( select * from t2 where t2.c > 'b1' )
+group by a1,a2,b;
+a1      a2      b       min(c)  max(c)
+a       a       a       a111    d111
+a       a       b       e112    h112
+a       b       a       i121    l121
+a       b       b       m122    p122
+b       a       a       a211    d211
+b       a       b       e212    h212
+b       b       a       i221    l221
+b       b       b       m222    p222
+c       a       a       a311    d311
+c       a       b       e312    h312
+c       b       a       i321    l321
+c       b       b       m322    p322
+d       a       a       a411    d411
+d       a       b       e412    h412
+d       b       a       i421    l421
+d       b       b       m422    p422
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      range   NULL    idx_t1_1        147     NULL    17      Using where; Using index for group-by
+2       DEPENDENT SUBQUERY      t2      index   NULL    idx_t2_1        163     NULL    164     Using where; Using index
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+a1      a2      b       c       min(c)  max(c)
+a       a       b       h112    e112    h112
+a       b       b       p122    m122    p122
+b       a       b       h212    e212    h212
+b       b       b       p222    m222    p222
+c       a       b       h312    e312    h312
+c       b       b       p322    m322    p322
+d       a       b       h412    e412    h412
+d       b       b       p422    m422    p422
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+where t2.c in (select c from t3 where t3.c > t1.b) and
+t2.c > 'b1' )
+group by a1,a2,b;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      range   NULL    idx_t1_1        147     NULL    17      Using where; Using index for group-by
+2       DEPENDENT SUBQUERY      t2      index   NULL    idx_t2_1        163     NULL    164     Using where; Using index
+2       DEPENDENT SUBQUERY      t3      index   NULL    idx_t3_1        10      NULL    192     Using where; Using index; FirstMatch(t2)
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+where t2.c in (select c from t3 where t3.c > t1.b) and
+t2.c > 'b1' )
+group by a1,a2,b;
+a1      a2      b       c       min(c)  max(c)
+a       a       a       d111    a111    d111
+a       a       b       h112    e112    h112
+a       b       a       l121    i121    l121
+a       b       b       p122    m122    p122
+b       a       a       d211    a211    d211
+b       a       b       h212    e212    h212
+b       b       a       l221    i221    l221
+b       b       b       p222    m222    p222
+c       a       a       d311    a311    d311
+c       a       b       h312    e312    h312
+c       b       a       l321    i321    l321
+c       b       b       p322    m322    p322
+d       a       a       d411    a411    d411
+d       a       b       h412    e412    h412
+d       b       a       l421    i421    l421
+d       b       b       p422    m422    p422
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      index   NULL    idx_t1_1        163     NULL    128     Using where; Using index
+2       DEPENDENT SUBQUERY      t2      index   NULL    idx_t2_1        163     NULL    164     Using where; Using index
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+a1      a2      b       c       min(c)  max(c)
+a       a       a       a111    a111    d111
+a       a       b       e112    e112    h112
+a       b       a       i121    i121    l121
+a       b       b       m122    m122    p122
+b       a       a       a211    a211    d211
+b       a       b       e212    e212    h212
+b       b       a       i221    i221    l221
+b       b       b       m222    m222    p222
+c       a       a       a311    a311    d311
+c       a       b       e312    e312    h312
+c       b       a       i321    i321    l321
+c       b       b       m322    m322    p322
+d       a       a       a411    a411    d411
+d       a       b       e412    e412    h412
+d       b       a       i421    i421    l421
+d       b       b       m422    m422    p422
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+where t2.c in (select c from t3 where t3.c > t1.c) and
+t2.c > 'b1' )
+group by a1,a2,b;
+id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1       PRIMARY t1      index   NULL    idx_t1_1        163     NULL    128     Using where; Using index
+2       DEPENDENT SUBQUERY      t2      index   NULL    idx_t2_1        163     NULL    164     Using where; Using index
+2       DEPENDENT SUBQUERY      t3      index   NULL    idx_t3_1        10      NULL    192     Using where; Using index; FirstMatch(t2)
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+where t2.c in (select c from t3 where t3.c > t1.c) and
+t2.c > 'b1' )
+group by a1,a2,b;
+a1      a2      b       c       min(c)  max(c)
+a       a       a       a111    a111    d111
+a       a       b       e112    e112    h112
+a       b       a       i121    i121    l121
+a       b       b       m122    m122    p122
+b       a       a       a211    a211    d211
+b       a       b       e212    e212    h212
+b       b       a       i221    i221    l221
+b       b       b       m222    m222    p222
+c       a       a       a311    a311    d311
+c       a       b       e312    e312    h312
+c       b       a       i321    i321    l321
+c       b       b       m322    m322    o322
+d       a       a       a411    a411    d411
+d       a       b       e412    e412    h412
+d       b       a       i421    i421    l421
+d       b       b       m422    m422    o422
 explain select a1,a2,b,min(c),max(c) from t1 where (a1 >= 'c' or a2 < 'b') and (b > 'a') group by a1,a2,b;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       SIMPLE  t1      range   idx_t1_0,idx_t1_1,idx_t1_2      idx_t1_1        147     NULL    17      Using where; Using index for group-by
@@ -2246,11 +2392,11 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer WHE
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
 1       PRIMARY t1_outer        index   NULL    a       10      NULL    15      Using index
-2       SUBQUERY        t1      index   NULL    a       10      NULL    15      Using index
+2       SUBQUERY        t1      index   NULL    a       10      NULL    1       Using index
 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
 (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) > 12;
 id      select_type     table   type    possible_keys   key     key_len ref     rows    Extra
-1       PRIMARY NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE
+1       PRIMARY t1_outer        index   NULL    a       10      NULL    15      Using index
 2       SUBQUERY        t1      range   NULL    a       5       NULL    8       Using index for group-by
 EXPLAIN SELECT 1 FROM t1 AS t1_outer WHERE 
 a IN (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2);

=== modified file 'mysql-test/t/group_min_max.test'
--- a/mysql-test/t/group_min_max.test	2010-06-11 07:38:29 +0000
+++ b/mysql-test/t/group_min_max.test	2011-01-17 22:53:41 +0000
@@ -406,11 +406,61 @@ explain select a1,a2,b,min(c),max(c) fro
 where exists ( select * from t2 where t2.c = t1.c )
 group by a1,a2,b;
 
+select a1,a2,b,min(c),max(c) from t1
+where exists ( select * from t2 where t2.c = t1.c )
+group by a1,a2,b;
+
 # the sub-select is unrelated to MIN/MAX
 explain select a1,a2,b,min(c),max(c) from t1
 where exists ( select * from t2 where t2.c > 'b1' )
 group by a1,a2,b;
 
+select a1,a2,b,min(c),max(c) from t1
+where exists ( select * from t2 where t2.c > 'b1' )
+group by a1,a2,b;
+
+# correlated subselect that doesn't reference the min/max argument
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.b > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+               where t2.c in (select c from t3 where t3.c > t1.b) and
+               t2.c > 'b1' )
+group by a1,a2,b;
+
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+               where t2.c in (select c from t3 where t3.c > t1.b) and
+               t2.c > 'b1' )
+group by a1,a2,b;
+
+# correlated subselect that references the min/max argument
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2 where t1.c > 'a' and t2.c > 'b1' )
+group by a1,a2,b;
+
+explain select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+               where t2.c in (select c from t3 where t3.c > t1.c) and
+               t2.c > 'b1' )
+group by a1,a2,b;
+
+select a1,a2,b,c,min(c), max(c) from t1
+where exists ( select * from t2
+               where t2.c in (select c from t3 where t3.c > t1.c) and
+               t2.c > 'b1' )
+group by a1,a2,b;
+
 
 # A,B,C) Predicates referencing mixed classes of attributes
 # plans

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-12-28 20:25:33 +0000
+++ b/sql/opt_range.cc	2011-01-17 22:53:41 +0000
@@ -11102,7 +11102,7 @@ static bool get_constant_key_infix(KEY *
                        uchar *key_infix, uint *key_infix_len,
                        KEY_PART_INFO **first_non_infix_part);
 static bool
-check_group_min_max_predicates(COND *cond, Item_field *min_max_arg_item,
+check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item,
                                Field::imagetype image_type);
 
 static void
@@ -11676,13 +11676,16 @@ get_best_group_min_max(PARAM *param, SEL
 */
 
 static bool
-check_group_min_max_predicates(COND *cond, Item_field *min_max_arg_item,
+check_group_min_max_predicates(Item *cond, Item_field *min_max_arg_item,
                                Field::imagetype image_type)
 {
   DBUG_ENTER("check_group_min_max_predicates");
   DBUG_ASSERT(cond && min_max_arg_item);
 
   cond= cond->real_item();
+  if (cond->get_cached_item())
+    cond= cond->get_cached_item();
+
   Item::Type cond_type= cond->type();
   if (cond_type == Item::COND_ITEM) /* 'AND' or 'OR' */
   {
@@ -11699,18 +11702,25 @@ check_group_min_max_predicates(COND *con
   }
 
   /*
-    TODO:
-    This is a very crude fix to handle sub-selects in the WHERE clause
-    (Item_subselect objects). With the test below we rule out from the
-    optimization all queries with subselects in the WHERE clause. What has to
-    be done, is that here we should analyze whether the subselect references
-    the MIN/MAX argument field, and disallow the optimization only if this is
-    so.
+    Disallow loose index scan if the MIN/MAX argument field is referenced by
+    a subquery in the WHERE clause.
   */
-  if (cond_type == Item::SUBSELECT_ITEM ||
-      (cond->get_cached_item() &&
-       cond->get_cached_item()->type() == Item::SUBSELECT_ITEM))
-    DBUG_RETURN(FALSE);
+  if (cond_type == Item::SUBSELECT_ITEM)
+  {
+    Item_subselect *subs_cond= (Item_subselect*) cond;
+    if (subs_cond->is_correlated)
+    {
+      DBUG_ASSERT(subs_cond->depends_on.elements > 0);
+      List_iterator_fast<Item*> li(subs_cond->depends_on);
+      Item **dep;
+      while ((dep= li++))
+      {
+        if ((*dep)->eq(min_max_arg_item, FALSE))
+          DBUG_RETURN(FALSE);
+      }
+    }
+    DBUG_RETURN(TRUE);
+  }
 
   /*
     Condition of the form 'field' is equivalent to 'field <> 0' and thus



More information about the commits mailing list