List:Commits« Previous MessageNext Message »
From:Ole John Aske Date:February 1 2011 10:27am
Subject:bzr commit into mysql-trunk branch (ole.john.aske:3553) Bug#41740 Bug#58225
Bug#59326
View as plain text  
#At file:///net/fimafeng09/export/home/tmp/oleja/mysql/mysql-trunk/ based on revid:ole.john.aske@stripped

 3553 Ole John Aske	2011-02-01
      Last addendum fix related to bug#59326, and particularly
      its relatives/duplicates bug#41740 and bug#58225. 
      
      If a filesort is required by the final result, we can include
      the cost for the filesort in the pruning of 'bad' plans, and
      possible prune the plan at an earlier stage than today.
      
      NOTE1: The 'sort_cost' is calculated with logic similar
             to the one in plan_is_complete() which adds the sort_cost
             to the best plan.
      
      NOTE2: The 'sort_cost' is calculated as '= current_record_count'.
             This is not entirely correct when used in the pruning logic
             as we do not yet #rows in the *final* result which is what 
             we have to sort. However, it should be a safe assumption that
             'final #rows' >= 'intermediat #rows'. So this prune-calculation
             should always be on the safe side.
      
      NOTE3: Calculating 'sort_cost= current_record_count' is a bad
             estimate as most sort algorithms are O(n*log(n)).
      
      This fix reduce the query compile time for the infamous bug#58225 problem
      further from 50ms to ~20ms

    modified:
      sql/sql_select.cc
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-02-01 09:43:41 +0000
+++ b/sql/sql_select.cc	2011-02-01 10:27:28 +0000
@@ -8325,7 +8325,31 @@ best_extension_by_limited_search(JOIN   
           backout_nj_sj_state(remaining_tables, s);
           continue;
         }
-      }
+
+        /*
+          There might later apply an addition cost for filesorting
+          if the result can't be ordered by using an ordered index.
+          Can prune if 'cost sofar + later_sort' already exceeds 'best_read'.
+          Note: Similar code in plan_is_complete().
+        */
+        if (idx > join->const_tables &&  // '->positions[const_tables]' valid?
+            join->sort_by_table &&
+            join->sort_by_table !=
+            join->positions[join->const_tables].table->table)
+        {
+          double sort_time = current_record_count;
+          if (current_read_time + sort_time >= join->best_read)
+          {
+            DBUG_EXECUTE("opt", print_plan(join, idx+1,
+                                           current_record_count,
+                                           read_time,
+                                           current_read_time,
+                                           "prune_by_sortcost_heuristic"););
+            backout_nj_sj_state(remaining_tables, s);
+            continue;
+          }
+        }
+      } // if (prune...)
 
       if ( (search_depth > 1) && (remaining_tables & ~real_table_bit) & allowed_tables )
       {


Attachment: [text/bzr-bundle] bzr/ole.john.aske@oracle.com-20110201102728-tbgbx14amhbrgi01.bundle
Thread
bzr commit into mysql-trunk branch (ole.john.aske:3553) Bug#41740 Bug#58225Bug#59326Ole John Aske1 Feb
  • Re: bzr commit into mysql-trunk branch (ole.john.aske:3553) Bug#41740Bug#58225 Bug#59326Olav Sandstaa19 Apr
  • Re: bzr commit into mysql-trunk branch (ole.john.aske:3553) Bug#41740Bug#58225 Bug#59326Evgeny Potemkin28 Apr