From c945233a192d559695b83a252b61168e7611ea03 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Tue, 2 Sep 2014 18:54:29 +0400 Subject: MDEV-6657: Poor plan choice for ORDER BY key DESC optimization... The problem was caused by the following scenario: - range optimizer picks an index IDX1 which doesn't match the ORDER BY ... LIMIT clause. - test_if_skip_sort_order() decides to switch to index IDX2 which matches the ORDER BY ... LIMIT. - it runs SQL_SELECT::test_quick_select() for the second time to produce an quick select for IDX2. - However, test_quick_select() would figure that full index scan on IDX1 is still cheaper (its calculations ignore the LIMIT n). Fixed this by - passing force_quick_range=true to test_quick_select() - in test_quick_select, don't consider full index scans if the mentioned parameter is true. Numerous changes in .result files are caused by test_quick_select() being run after "early/late NULLs filtering" feature has injected NOT NULL condition. --- mysql-test/r/subselect_sj_mat.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/r/subselect_sj_mat.result') diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result index 95dfc34777b..7417ab56ff8 100644 --- a/mysql-test/r/subselect_sj_mat.result +++ b/mysql-test/r/subselect_sj_mat.result @@ -107,7 +107,7 @@ select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1i index it1i1,it1i3 # 18 # 3 100.00 # 1 PRIMARY eq_ref distinct_key # 8 # 1 100.00 # -2 MATERIALIZED t2i index it2i1,it2i3 # 9 # 5 100.00 # +2 MATERIALIZED t2i range it2i1,it2i3 # 9 # 5 100.00 # Warnings: Note 1003 select `test`.`t1i`.`a1` AS `a1`,`test`.`t1i`.`a2` AS `a2` from (select max(`test`.`t2i`.`b1`) from `test`.`t2i` where (`test`.`t2i`.`b1` > '0') group by `test`.`t2i`.`b1`) join `test`.`t1i` where (``.`max(b1)` = `test`.`t1i`.`a1`) select * from t1i where a1 in (select max(b1) from t2i where b1 > '0' group by b1); -- cgit v1.2.1