From: Ole John Aske Date: January 25 2012 12:46pm Subject: bzr push into mysql-trunk branch (ole.john.aske:3782 to 3783) Bug#13514959 List-Archive: http://lists.mysql.com/commits/142556 X-Bug: 13514959 Message-Id: <20120125124626.05BFB235@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3783 Ole John Aske 2012-01-25 Fix for Bug#13514959 QUERY PLAN FAILS TO 'USING INDEX' WHERE IT SHOULD Fix two issues related to 'keyread' from covering indexes (Explain: 'Using index') 1) Recalculate (and set) keyread in test_if_skip_sort_order() whenever the best_key is changed (Optimizer may have changed its mind) 2) If we during cleanup in test_if_skip_sort_order() decides to delete the 'save_quick' we had when entering test_if_skip_sort_order(), the ::~QUICK_RANGE_SELECT d'tor will set_keyread(FALSE)' even if another quick was set up to be used in 1) above. @ mysql-test/r/innodb_icp.result Updated resultfile where 'Using index' is expected @ mysql-test/r/innodb_icp_none.result Updated resultfile where 'Using index' is expected @ sql/opt_range.cc ::~QUICK_RANGE_SELECT Should not 'set_keyread(FALSE)' as that has nothing to do with destruction of the QUICK_RANGE_SELECT. ('head' might actually use another 'quick') @ sql/records.cc If 'keyread' is used, ut should be cleared when we clean up after have read this table. (Instead of doing it as a side effect in QUICK_RANGE_SELECT d'tor) @ sql/sql_select.cc Fix code for setting of set_keyread() in test_if_skip_sort_order(). Always recalculate it if 'best_key' is changed. ****** Don't apply join-fanout and predicate selctivite if there was no limit-clause ****** Fix up EXPLAIN to use the 'ordered_index_usage' as calculated by ::optimize() instead of calling test_if_skip_sort_order() modified: mysql-test/r/innodb_icp.result mysql-test/r/innodb_icp_none.result sql/opt_range.cc sql/records.cc sql/sql_select.cc 3782 Martin Hansson 2012-01-25 Bug#13536661: Deactivates the test case until a better place for it is found. modified: mysql-test/r/temp_table.result mysql-test/t/temp_table.test === modified file 'mysql-test/r/innodb_icp.result' --- a/mysql-test/r/innodb_icp.result 2012-01-03 11:04:14 +0000 +++ b/mysql-test/r/innodb_icp.result 2012-01-25 12:45:56 +0000 @@ -992,7 +992,7 @@ FROM t2 JOIN t1 ON t2.col_int_key WHERE t2.pk < 7 AND t2.col_int_key <> 7 GROUP BY field1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY,col_int_key col_int_key 5 NULL 5 Using where +1 SIMPLE t2 range PRIMARY,col_int_key col_int_key 5 NULL 5 Using where; Using index 1 SIMPLE t1 index NULL col_int_key 5 NULL 4 Using index SELECT t2.col_int_key AS field1 FROM t2 JOIN t1 ON t2.col_int_key === modified file 'mysql-test/r/innodb_icp_none.result' --- a/mysql-test/r/innodb_icp_none.result 2012-01-03 11:04:14 +0000 +++ b/mysql-test/r/innodb_icp_none.result 2012-01-25 12:45:56 +0000 @@ -991,7 +991,7 @@ FROM t2 JOIN t1 ON t2.col_int_key WHERE t2.pk < 7 AND t2.col_int_key <> 7 GROUP BY field1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2 range PRIMARY,col_int_key col_int_key 5 NULL 5 Using where +1 SIMPLE t2 range PRIMARY,col_int_key col_int_key 5 NULL 5 Using where; Using index 1 SIMPLE t1 index NULL col_int_key 5 NULL 4 Using index SELECT t2.col_int_key AS field1 FROM t2 JOIN t1 ON t2.col_int_key === modified file 'sql/opt_range.cc' --- a/sql/opt_range.cc 2012-01-12 13:22:52 +0000 +++ b/sql/opt_range.cc 2012-01-25 12:45:56 +0000 @@ -1342,7 +1342,6 @@ QUICK_RANGE_SELECT::~QUICK_RANGE_SELECT( if (file) { range_end(); - head->set_keyread(FALSE); if (free_file) { DBUG_PRINT("info", ("Freeing separate handler %p (free: %d)", file, === modified file 'sql/records.cc' --- a/sql/records.cc 2011-12-14 12:32:55 +0000 +++ b/sql/records.cc 2012-01-25 12:45:56 +0000 @@ -325,6 +325,10 @@ void end_read_record(READ_RECORD *info) my_free_lock(info->cache); info->cache=0; } + if (info->table && info->table->key_read) + { + info->table->set_keyread(FALSE); + } if (info->table && info->table->created) { filesort_free_buffers(info->table,0); === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2012-01-24 11:24:54 +0000 +++ b/sql/sql_select.cc 2012-01-25 12:45:56 +0000 @@ -3976,11 +3976,16 @@ check_reverse_order: (select && select->quick && select->quick!=save_quick); /* - If ref_key used index tree reading only ('Using index' in EXPLAIN), - and best_key doesn't, then revert the decision. + If 'best_key' has changed from prev. 'ref_key': + Update strategy for using index tree reading only + ('Using index' in EXPLAIN) */ - if (!table->covering_keys.is_set(best_key)) - table->set_keyread(FALSE); + if (best_key != ref_key) + { + const bool using_index= + (table->covering_keys.is_set(best_key) && !table->no_keyread); + table->set_keyread(using_index); + } if (!quick_created) { if (select) // Throw any existing quick select @@ -3991,8 +3996,6 @@ check_reverse_order: join_read_first:join_read_last; tab->type=JT_INDEX_SCAN; // Read with index_first(), index_next() - if (table->covering_keys.is_set(best_key)) - table->set_keyread(TRUE); table->file->ha_index_or_rnd_end(); if (tab->join->select_options & SELECT_DESCRIBE) { @@ -4010,6 +4013,7 @@ check_reverse_order: method is actually used. */ DBUG_ASSERT(tab->select->quick); + DBUG_ASSERT(tab->select->quick->index==(uint)best_key); tab->type=JT_ALL; tab->use_quick=QS_RANGE; tab->ref.key= -1; No bundle (reason: useless for push emails).