MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:September 15 2007 3:05pm
Subject:bk commit into 5.2 tree (sergefp:1.2599) BUG#30363
View as plain text  
Below is the list of changes that have just been committed into a local
5.2 repository of psergey. When psergey does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-09-15 19:05:18+04:00, sergefp@stripped +3 -0
  BUG#30363: complex query slower by 2.6x to 6x times in 5.2 then in 5.1
  The bugfix is a fix for the incorrect merge:
   - Make make_join_orderinfo() determine the last table that can use the 
     join buffering in exactly the same way as it is done in 
     make_join_readinfo() in current 5.1
   - Remove now unneeded code from make_join_readinfo()

  mysql-test/r/innodb_mysql.result@stripped, 2007-09-15 19:05:12+04:00, sergefp@stripped +1 -1
    BUG#30363: Updated (previously incorrect) test result

  sql/sql_select.cc@stripped, 2007-09-15 19:05:13+04:00, sergefp@stripped +36 -28
    BUG#30363: complex query slower by 2.6x to 6x times in 5.2 then in 5.1
    The bugfix is a fix for the incorrect merge:
     - Make make_join_orderinfo() determine the last table that can use the 
       join buffering in exactly the same way as it is done in 
       make_join_readinfo() in current 5.1
     - Remove now unneeded code from make_join_readinfo()

  sql/sql_select.h@stripped, 2007-09-15 19:05:13+04:00, sergefp@stripped +8 -1
    BUG#30363: complex query slower by 2.6x to 6x times in 5.2 then in 5.1
    - Added comments

diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result	2007-09-06 19:49:54 +04:00
+++ b/mysql-test/r/innodb_mysql.result	2007-09-15 19:05:12 +04:00
@@ -980,7 +980,7 @@ key	PRIMARY
 key_len	4
 ref	NULL
 rows	16
-Extra	
+Extra	Using where; Using index
 SELECT * FROM t2 WHERE b=1 ORDER BY a;
 a	b	c
 1	1	1
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-09-06 21:54:49 +04:00
+++ b/sql/sql_select.cc	2007-09-15 19:05:13 +04:00
@@ -7989,20 +7989,29 @@ static void push_index_cond(JOIN_TAB *ta
   DBUG_VOID_RETURN;
 }
 
+
+
+    /*
+      Determine if the set is already ordered for ORDER BY, so it can 
+      disable join cache because it will change the ordering of the results.
+      Code handles sort table that is at any location (not only first after 
+      the const tables) despite the fact that it's currently prohibited.
+      We must disable join cache if the first non-const table alone is
+      ordered. If there is a temp table the ordering is done as a last
+      operation and doesn't prevent join cache usage.
+    */
 uint make_join_orderinfo(JOIN *join)
 {
   uint i;
+  if (join->need_tmp)
+    return join->tables;
+
   for (i=join->const_tables ; i < join->tables ; i++)
   {
     JOIN_TAB *tab=join->join_tab+i;
     TABLE *table=tab->table;
-    if ((table == join->sort_by_table &&
-         (!join->order || join->skip_sort_order ||
-          test_if_skip_sort_order(tab, join->order, join->select_limit,
-                                  FALSE, &table->keys_in_use_for_order_by)) 
-         //psergey-merge-todo: ^ check what should be instead of the above
-         // FALSE! check the last argument!
-        ) ||
+    if ((table == join->sort_by_table && 
+         (!join->order || join->skip_sort_order)) ||
         (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))
     {
       break;
@@ -8013,12 +8022,25 @@ uint make_join_orderinfo(JOIN *join)
 
 
 /*
-  ...
+  Plan refinement stage: do various set ups for the executioner
+
   SYNOPSIS
     make_join_readinfo()
-      join
-      options
-      no_jbuf_after  X.
+      join           Join being processed
+      options        Join's options (checking for SELECT_DESCRIBE, 
+                     SELECT_NO_JOIN_CACHE)
+      no_jbuf_after  Don't use join buffering after table with this number.
+
+  DESCRIPTION
+    Plan refinement stage: do various set ups for the executioner
+      - set up use of join buffering
+      - push index conditions
+      - increment counters
+      - etc
+
+  RETURN 
+    FALSE - OK
+    TRUE  - Out of memory
 */
 
 static bool
@@ -8026,7 +8048,6 @@ make_join_readinfo(JOIN *join, ulonglong
 {
   uint i;
   bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
-  bool ordered_set= 0; //psergey-merge-todo: sort this out!
   bool sorted= 1;
   DBUG_ENTER("make_join_readinfo");
 
@@ -8038,23 +8059,10 @@ make_join_readinfo(JOIN *join, ulonglong
     tab->read_record.table= table;
     tab->read_record.file=table->file;
     tab->next_select=sub_select;		/* normal select */
-
-    /*
-      Determine if the set is already ordered for ORDER BY, so it can 
-      disable join cache because it will change the ordering of the results.
-      Code handles sort table that is at any location (not only first after 
-      the const tables) despite the fact that it's currently prohibited.
-      We must disable join cache if the first non-const table alone is
-      ordered. If there is a temp table the ordering is done as a last
-      operation and doesn't prevent join cache usage.
+    /* 
+      TODO: don't always instruct first table's ref/range access method to 
+      produce sorted output.
     */
-    if (!ordered_set && !join->need_tmp &&
-        ((table == join->sort_by_table &&
-         (!join->order || join->skip_sort_order)) ||
-        (join->sort_by_table == (TABLE *) 1 && i != join->const_tables)))
-      ordered_set= 1;
-
-
     tab->sorted= sorted;
     sorted= 0;                                  // only first must be sorted
     if (tab->insideout_match_tab)
diff -Nrup a/sql/sql_select.h b/sql/sql_select.h
--- a/sql/sql_select.h	2007-09-04 22:30:37 +04:00
+++ b/sql/sql_select.h	2007-09-15 19:05:13 +04:00
@@ -242,6 +242,7 @@ typedef struct st_join_table {
   uint		used_fields,used_fieldlength,used_blobs;
   enum join_type type;
   bool		cached_eq_ref_table,eq_ref_table,not_used_in_distinct;
+  /* TRUE <=> index-based access method must return records in order */
   bool		sorted;
   /* 
     If it's not 0 the number stored this field indicates that the index
@@ -403,7 +404,13 @@ public:
   JOIN_TAB *join_tab,**best_ref;
   JOIN_TAB **map2table;    // mapping between table indexes and JOIN_TABs
   JOIN_TAB *join_tab_save; // saved join_tab for subquery reexecution
-  TABLE    **table,**all_tables,*sort_by_table;
+  TABLE    **table,**all_tables;
+  /*
+    The table which has an index that allows to produce the requried ordering.
+    A special value of 0x1 means that the ordering will be produced by
+    passing 1st non-const table to filesort(). NULL means no such table exists.
+  */
+  TABLE    *sort_by_table;
   uint	   tables;        /* Number of tables in the join */
   uint     outer_tables;  /* Number of tables that are not inside semijoin */
   uint     const_tables;
Thread
bk commit into 5.2 tree (sergefp:1.2599) BUG#30363Sergey Petrunia15 Sep