From: Ole John Aske Date: January 25 2012 2:38pm Subject: bzr push into mysql-trunk branch (ole.john.aske:3783 to 3784) Bug#13529048 List-Archive: http://lists.mysql.com/commits/142551 X-Bug: 13529048 Message-Id: <20120125143812.4A7FE235@fimafeng09.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3784 Ole John Aske 2012-01-25 Fix for bug#13529048 TEST_IF_SKIP_SORT_ORDER() DON'T HAVE TO FILESORT A SINGLE ROW. test_if_skip_sort_order() should catch the jointypes JT_EQ_REF, JT_CONST and JT_SYSTEM and skip sort order for these. Didn't add a specific testcase for this bug as it should be covered by existing testcases which now will skip 'Using filesort' modified: mysql-test/r/innodb_mrr.result mysql-test/r/innodb_mrr_cost.result mysql-test/r/innodb_mrr_cost_icp.result mysql-test/r/innodb_mrr_icp.result mysql-test/r/innodb_mrr_none.result sql/sql_select.cc 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 === modified file 'mysql-test/r/innodb_mrr.result' --- a/mysql-test/r/innodb_mrr.result 2011-11-23 19:55:56 +0000 +++ b/mysql-test/r/innodb_mrr.result 2012-01-25 14:37:43 +0000 @@ -539,7 +539,7 @@ WHERE t2.i1 > 5 AND t2.pk IS NULL ORDER BY i1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 const PRIMARY,k1 PRIMARY 4 const 1 Using where SELECT i1 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2 === modified file 'mysql-test/r/innodb_mrr_cost.result' --- a/mysql-test/r/innodb_mrr_cost.result 2011-11-23 19:55:56 +0000 +++ b/mysql-test/r/innodb_mrr_cost.result 2012-01-25 14:37:43 +0000 @@ -539,7 +539,7 @@ WHERE t2.i1 > 5 AND t2.pk IS NULL ORDER BY i1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 const PRIMARY,k1 PRIMARY 4 const 1 Using where SELECT i1 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2 === modified file 'mysql-test/r/innodb_mrr_cost_icp.result' --- a/mysql-test/r/innodb_mrr_cost_icp.result 2011-12-01 14:12:10 +0000 +++ b/mysql-test/r/innodb_mrr_cost_icp.result 2012-01-25 14:37:43 +0000 @@ -539,7 +539,7 @@ WHERE t2.i1 > 5 AND t2.pk IS NULL ORDER BY i1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 const PRIMARY,k1 PRIMARY 4 const 1 Using where SELECT i1 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2 === modified file 'mysql-test/r/innodb_mrr_icp.result' --- a/mysql-test/r/innodb_mrr_icp.result 2011-12-01 14:12:10 +0000 +++ b/mysql-test/r/innodb_mrr_icp.result 2012-01-25 14:37:43 +0000 @@ -539,7 +539,7 @@ WHERE t2.i1 > 5 AND t2.pk IS NULL ORDER BY i1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 const PRIMARY,k1 PRIMARY 4 const 1 Using where SELECT i1 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2 === modified file 'mysql-test/r/innodb_mrr_none.result' --- a/mysql-test/r/innodb_mrr_none.result 2011-11-23 19:55:56 +0000 +++ b/mysql-test/r/innodb_mrr_none.result 2012-01-25 14:37:43 +0000 @@ -538,7 +538,7 @@ WHERE t2.i1 > 5 AND t2.pk IS NULL ORDER BY i1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 const PRIMARY,k1 PRIMARY 4 const 1 Using where SELECT i1 FROM t1 LEFT JOIN t2 ON t1.pk = t2.i2 === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2012-01-25 12:45:56 +0000 +++ b/sql/sql_select.cc 2012-01-25 14:37:43 +0000 @@ -3732,6 +3732,14 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR Plan_change_watchdog watchdog(tab, no_changes); + /* Sorting a single row can always be skipped */ + if (tab->type == JT_EQ_REF || + tab->type == JT_CONST || + tab->type == JT_SYSTEM) + { + DBUG_RETURN(1); + } + /* Keys disabled by ALTER TABLE ... DISABLE KEYS should have already been taken into account. No bundle (reason: useless for push emails).