bzr commit into mysql-trunk branch (jorgen.loland:3527) Bug#59415

[Jorgen Loland]

#At file:///export/home/jl208045/mysql/mysql-trunk-59415/ based on revid:alexander.barkov@stripped

 3527 Jorgen Loland	2011-01-18
      BUG#59415: Range analysis should not be done many times for the
                 same index
      
      The optimizer often choses to access a table through an index 
      that does not provide the correct ordering for free. To remedy 
      this, the function test_if_skip_sort_order() is called to see 
      if another index is as good as the chosen index and at the 
      same time is able to provide ordering.
      
      This implies that test_if_skip_sort_ordering() goes through a 
      full range analysis (if range access is applicable) to check 
      whether or not another range access plan should be used 
      instead of the currently chosen ref/range access method.
      
      The problem is that if range analysis is performed and it is 
      decided that it is not better than whatever we had, the range 
      analysis will most likely be performed again and again with 
      the same outcome because test_if_skip_sort_order() is called 
      from multiple locations.
      
      This patch avoids the unnecessarily repeated range analysis
      described above by introducing key_map 
      JOIN_TAB::quick_order_tested which is checked to see
      if range analysis has already been performed for a given key.
     @ sql/sql_select.h
        Introduce JOIN_TAB::quick_order_tested used to avoid repeated range analysis for the same key in test_if_skip_sort_order()

    modified:
      sql/sql_select.cc
      sql/sql_select.h
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-01-14 08:20:08 +0000
+++ b/sql/sql_select.cc	2011-01-18 09:07:10 +0000
@@ -19973,8 +19973,11 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
     if (best_key >= 0)
     {
       bool quick_created= FALSE;
-      if (table->quick_keys.is_set(best_key) && best_key != ref_key)
+      if (table->quick_keys.is_set(best_key) &&
+          !tab->quick_order_tested.is_set(best_key) &&
+          best_key != ref_key)
       {
+        tab->quick_order_tested.set_bit(best_key);
         key_map map;           // Force the creation of quick select
         map.set_bit(best_key); // only best_key.
         quick_created=         

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-01-14 08:20:08 +0000
+++ b/sql/sql_select.h	2011-01-18 09:07:10 +0000
@@ -279,6 +279,15 @@ typedef struct st_join_table : public Sq
   key_map	checked_keys;			/**< Keys checked */
   key_map	needed_reg;
   key_map       keys;                           /**< all keys with can be used */
+  /**
+    Used to avoid repeated range analysis for the same key in
+    test_if_skip_sort_order(). This would otherwise happen if the best
+    range access plan found for a key is turned down.
+    quick_order_tested is cleared every time the select condition for
+    this jointab changes since a new condition may give another plan
+    and cost from range analysis.
+   */
+  key_map       quick_order_tested;
 
   /* Either #rows in the table or 1 for const table.  */
   ha_rows	records;
@@ -421,6 +430,7 @@ typedef struct st_join_table : public Sq
     DBUG_PRINT("info", ("select_cond changes %p -> %p at line %u tab %p",
                         select_cond, to, line, this));
     select_cond= to;
+    quick_order_tested.clear_all();
   }
   Item *set_cond(Item *new_cond, uint line)
   {
@@ -462,6 +472,7 @@ st_join_table::st_join_table()
     checked_keys(),
     needed_reg(),
     keys(),
+    quick_order_tested(),
 
     records(0),
     found_records(0),


Attachment: [text/bzr-bundle] bzr/[e-mail]