3355 Jorgen Loland 2011-05-02
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
3354 Jorgen Loland 2011-05-02
BUG#11766327: Range analysis should not be done many times
for the same index
(formerly 59415)
Pre-bugfix changes:
* Make JOIN_TAB::select_cond private, add getter
* Rename JOIN_TAB::select_cond to m_condition
* Rename JOIN_TAB::pre_idx_push_select_cond to
pre_idx_push_cond
* Make use of and_with_condition() where possible to
simplify code
* Rename JOIN_TAB::set_select_cond() to
set_condition() since it sets JOIN_TAB->m_condition, not
join_tab->select->cond as the name suggested
* Rename JOIN_TAB::set_cond() to
set_jt_and_sel_condition() since it sets
join_tab->m_condition AND join_tab->select->cond
@ sql/sql_select.cc
JOIN_TAB::cond made private, rename JOIN_TAB functions for getting and setting condition to better reflect reality, use and_with_condition() where possible to simplify code.
@ sql/sql_select.h
Fix naming of JOIN_TAB variables and functions to better reflect reality. Make JOIN_TAB::m_condition (formerly select_cond) private.
@ sql/sql_show.cc
JOIN_TAB::m_condition (formerly select_cond) is made private
modified:
sql/sql_select.cc
sql/sql_select.h
sql/sql_show.cc
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-05-02 11:16:36 +0000
+++ b/sql/sql_select.cc 2011-05-02 11:51:41 +0000
@@ -20318,8 +20318,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-05-02 11:16:36 +0000
+++ b/sql/sql_select.h 2011-05-02 11:51:41 +0000
@@ -302,6 +302,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;
@@ -448,6 +457,7 @@ public:
("JOIN_TAB::m_condition changes %p -> %p at line %u tab %p",
m_condition, to, line, this));
m_condition= to;
+ quick_order_tested.clear_all();
}
Item *set_jt_and_sel_condition(Item *new_cond, uint line)
@@ -505,6 +515,7 @@ st_join_table::st_join_table()
checked_keys(),
needed_reg(),
keys(),
+ quick_order_tested(),
records(0),
found_records(0),
No bundle (reason: useless for push emails).

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.