From: Jorgen Loland
Date: February 16 2011 9:43am
Subject: bzr commit into mysql-trunk branch (jorgen.loland:3654) Bug#11766327
List-Archive: http://lists.mysql.com/commits/131392
X-Bug: 11766327
Message-Id: <20110216094353.67F6CAE6@atum21.norway.sun.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============8075953644216193656=="

--===============8075953644216193656==
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

 3654 Jorgen Loland	2011-02-16
      BUG#11766327: Range analysis should not be done many times 
                    for the same index
      
      (Former bug 59415) 
      
      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-02-16 09:27:55 +0000
+++ b/sql/sql_select.cc	2011-02-16 09:43:49 +0000
@@ -20096,8 +20096,11 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
 
     if (best_key >= 0)
     {
-      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.
         select->quick= 0;

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-02-16 09:27:55 +0000
+++ b/sql/sql_select.h	2011-02-16 09:43:49 +0000
@@ -281,6 +281,15 @@ public:
   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 JOIN_TAB 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;
@@ -427,6 +436,7 @@ public:
     DBUG_PRINT("info", ("JOIN_TAB::cond changes %p -> %p at line %u tab %p",
                         cond, to, line, this));
     cond= to;
+    quick_order_tested.clear_all();
   }
   Item *set_jt_and_sel_cond(Item *new_cond, uint line)
   {
@@ -470,6 +480,7 @@ st_join_table::st_join_table()
     checked_keys(),
     needed_reg(),
     keys(),
+    quick_order_tested(),
 
     records(0),
     found_records(0),


--===============8075953644216193656==
MIME-Version: 1.0
Content-Type: text/bzr-bundle; charset="us-ascii";
	name="bzr/jorgen.loland@stripped"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

# Bazaar merge directive format 2 (Bazaar 0.90)
# revision_id: jorgen.loland@stripped\
#   hqx5kfor0al5ddl4
# target_branch: file:///export/home/jl208045/mysql/mysql-trunk-59415/
# testament_sha1: f648adf651191bd1f88bce0bc7388e1d113c4108
# timestamp: 2011-02-16 10:43:53 +0100
# base_revision_id: jorgen.loland@stripped\
#   imn73k3ssxqv1qta
# 
# Begin bundle
IyBCYXphYXIgcmV2aXNpb24gYnVuZGxlIHY0CiMKQlpoOTFBWSZTWSQTI4cAAwrfgF0QeXf//3O1
lgC////wYAgOcLd8kQW7LduYhNmtsk0MuGSJlT1B6nqbU9TPVPGqfpTyQGnpHqNANGg0yAkkmE0y
TCaNIyU3lPVP1Rp6m1NqB6QNAABqp+BNJqR6hozSaZAADRkBoADQCUyQUZNKNqeo0zTSeoAaaAGg
aHqAA5gTE0GEyZMmRhME00yMTAEMAkiJiJPJkyaJgk2ieiTyaQNGg0AD1GjMhB1pVTZgrM69D8yf
0ddNHwqA/F6YhaziG/FyWP4Mc+y3HWue65BMiF+uuONOvGy46V5LO9/BIQaMPzOaX0/KjW76/78X
ADQlEGCgaKYmPBvscxvOESQkHL9wvuY9exltYCsIKpGoJviUllervW8m0LNwM4lwoAYRkAEYGurc
kFECFsVzJKjkO2DR6JL+aZIRggxi44ww1IhcWxCgmkb1EyiEdX0oFc3bPCrApTQwswWeerEJ10cE
cFM0JHDIU1MJqSPVaKEEFJ9063Fu1UAoraDJysrBlbMqaHd+UtE0EYjuaqMljrg6Nb3bW/LltdAV
tHKxwC0BGe4SDA9BMj+agtLvDJ2LCzQg5vPzl4ctmhwXVCxO1/I9ojntzRCcFnU9aKOnrgY3cbLV
SIlogicaxJssVCUy3gXeUwLWQ7NyR5npCcCSGghkJgRAr3khLuRMqRJbOQHhDrJPXUvQzPYb5WS8
ooThYm6/s9Cy9IR7x84879IMNHIPJY4e6uJqhc4R6e6fD3oiVKjje5FD/kU78dtiLkLWhVFnwqu5
FaOUTIbMpqi6WpqbDbeVz4q+jImxeWkgOO+zZgcIVz2WLAMCSMzcjgUUU6MYGrWy16SprXcBopyF
pkZDqpzMnJoxAzc90kW+p6m6NEsegxvkODjoiJYXI4SwNqMtal+m4YLgNLPqA+Q2VaTpzQu+BYQy
McSBm/gdMDG3QTK+RsNM4LAgh+DIdG5+z1GGvZESWSJmzvOS6mK6Y8VK3S6W7zjYHAv3m6sS/fSD
X2mjg2EKz2Z347cH33FwDJ0CjqoYeQsAcMh+6qIvKxbLEtjOUy6lipVzUrk1GFXEdNJgi/Mo8FbK
aKVt7sG7Vh+P0W6xtxRBYxXC60sBOo+8kaIURFDDoiv16iyYRqV9nSVfpYAo3b/5Hi7OnhYxEc6j
0zB0EhIC8/PzAMkLwe2CwC5G6gvFyWvyh0PA8qvfae8bq+DuLTpEsr/Mcl6DBMxJo+RkB8eVpPcg
ohivaRa50uTdUXOLOKJiD+q1I8cN7NRriMq1LMG6VijnSVh7sTOejYMYcQpDhVeL6lwEpRKkHt0F
UIjCUDuSxogQHTIEk3czjvvmVf9ivR6dWvRDrODTE+LSeIc4StgwlLGwN3hLkGFGNCWh7VMJCeCm
MRAT6y0xrw1XKw6Lf4HVQKq5ocS9kDdcU6wJm206zRH2hRkiHUHemOwDQa1QyWmxjtb28SAL5dIu
ywzH/XdII31YvlYlF74sz4OVGAN9C+1MjiOJdNeRUp6t6deqofs7nnBSrnskRkyDONSUHbqBOR4z
elyJYRJ7ej9ktzYSqN9Z96y9l1rd37OwQXZ0126x5WugJRB6ZLdt7mVdWxEYOBYq3TylPjZyelIb
kSG2aEMEHAkrIRj2XIvWegKkagVOoVu9GLGbjNFHP7+nfvPYsZrrP7J+0bMoTWRAKmNQDEs05FtT
xhuXjQOZiOhOlWkngokjrWA5qNthwO4GhePxFOMHHDowveMllAtsRAaCYepDzsA82pMGOZosudmm
iCBPxF6zY986fvf7W0LkfGpUO4DPGgqomu9m04IkMq34ET1K7eKmMYQLCBOy7DiHDTaaExcRpvRU
cIYnfUcChDXcj8DmmHwyODdiGEUOe/BMDJvEDuHRciouXuDmGIFSOQFUV9cgK1zSVDqttCHcDshJ
kMXIKD7l+SkZbJ0d6mHP5CVlz9HKlSCF4BTE7U8KpMmZRUmhgNJs27ijo2lVc4pmNsVpWpglmlpD
Q5lt9qhEPGBRWAuWelAzOQwkwPXGjovFAS6yIKAPVSQ/kW0S9HDhxHWCrbFHRh7zRr6u6C5abzFd
y/6sSmewAvAYr85qNkizYtIOyR/olgjgCizfL1EpC9dO1HOa3J+u9KmuJE+KsO0DXVcSmCWqLebD
AzPXgs3rDsYlithJrjpYQuYnmMqgvoUzzR9PzTGHH++KMQLFqZmYvZOmQwDKKg4EEgz0nnY4EUl3
7addSdQYHV3k60KiSbh2zgopZm37DPsVxXDIYpNoM6u2kccbJUbFkgEhpKUMTSnlZIaV0dUsgUMA
1PQjafNpLADSZM56Q8GIFSoi5BBK0L7yCPpN7M1dhR4QGZR0gfBDXDCK2AmLAWVSyeDaRnhGBIqn
UBGMCSDcjkH7euX29ETs20gQTJctpRdrbQJOHbqhDiyEzGWLKkYROhKWMVmc7jWVdZJZIcLvucF8
4saHguuBqgZbzozqTv1yyeR8jEojB7WLEsPYORkTTgmat+I2DWtNRCgG1XowUUZNm7WWxNbRjZAH
ZmPiNFBncqSWINyGcSj/F3JFOFCQJBMjhw==


--===============8075953644216193656==--