revision-id: b729f8c536bc2307ee3008167d009f440ab6b725 (mariadb-10.1.39-155-gb729f8c536b) parent(s): de0f93fb0d7b03aaf293cc89b611aeff3ce3244e author: Varun Gupta committer: Varun Gupta timestamp: 2019-08-26 20:54:17 +0530 message: MDEV-18094: Query with order by limit picking index scan over filesort In the function test_if_cheaper_ordering we make a decision if using an index is better than using filesort for ordering. If we chose to do range access then in test_quick_select we should make sure that cost for table scan is set to DBL_MAX so that it is not picked --- mysql-test/r/group_min_max.result | 4 ++-- mysql-test/r/order_by_innodb.result | 31 +++++++++++++++++++++++++++++++ mysql-test/t/order_by_innodb.test | 24 ++++++++++++++++++++++++ sql/opt_range.cc | 14 +++++++++----- 4 files changed, 66 insertions(+), 7 deletions(-) diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 777780f8400..c535550d5f9 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2119,12 +2119,12 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL idx_t1_2 147 NULL 128 Using index explain extended select a1,a2,count(a2) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 102 94.12 Using where; Using index Warnings: Note 1003 select `test`.`t1`.`a1` AS `a1`,`test`.`t1`.`a2` AS `a2`,count(`test`.`t1`.`a2`) AS `count(a2)` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` explain extended select sum(ord(a1)) from t1 where (a1 > 'a') group by a1,a2,b; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 index idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 147 NULL 128 75.00 Using where; Using index +1 SIMPLE t1 range idx_t1_0,idx_t1_1,idx_t1_2 idx_t1_2 65 NULL 102 94.12 Using where; Using index Warnings: Note 1003 select sum(ord(`test`.`t1`.`a1`)) AS `sum(ord(a1))` from `test`.`t1` where (`test`.`t1`.`a1` > 'a') group by `test`.`t1`.`a1`,`test`.`t1`.`a2`,`test`.`t1`.`b` create table t4 as select distinct a1, a2, b, c from t1; diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 4f59a2f8c20..4c997769b65 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -48,3 +48,34 @@ where key1<3 or key2<3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge key1,key2 key1,key2 5,5 NULL # Using sort_union(key1,key2); Using where drop table t0, t1; +# +# MDEV-18094: Query with order by limit picking index scan over filesort +# +create table t0 (a int); +INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0); +CREATE TABLE t1 ( +a int(11) DEFAULT NULL, +b int(11) DEFAULT NULL, +c int(11) DEFAULT NULL, +KEY a_c (a,c), +KEY a_b (a,b) +) ENGINE=InnoDB; +insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C; +# should use ref access +explain extended select a,b,c from t1 where a=1 and c=2 order by b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ref a_c,a_b a_c 10 const,const 20 100.00 Using where; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`c` = 2)) order by `test`.`t1`.`b` +# both should use range access +explain extended select a,b,c from t1 where a=1 and c=2 order by b limit 1000; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a_c,a_b a_b 5 NULL 200 10.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`c` = 2)) order by `test`.`t1`.`b` limit 1000 +explain extended select a,b,c from t1 where a=1 and c=2 order by b limit 2000; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 range a_c,a_b a_b 5 NULL 200 10.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where ((`test`.`t1`.`a` = 1) and (`test`.`t1`.`c` = 2)) order by `test`.`t1`.`b` limit 2000 +drop table t1,t0; diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index 097eddd24f1..7d8b5c2ca65 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -61,3 +61,27 @@ from t1 where key1<3 or key2<3; drop table t0, t1; + +--echo # +--echo # MDEV-18094: Query with order by limit picking index scan over filesort +--echo # + +create table t0 (a int); +INSERT INTO t0 VALUES (0),(0),(0),(0),(2),(0),(0),(1),(1),(0); + +CREATE TABLE t1 ( +a int(11) DEFAULT NULL, +b int(11) DEFAULT NULL, +c int(11) DEFAULT NULL, +KEY a_c (a,c), +KEY a_b (a,b) +) ENGINE=InnoDB; +insert into t1 select A.a , B.a, C.a from t0 A, t0 B, t0 C; + +--echo # should use ref access +explain extended select a,b,c from t1 where a=1 and c=2 order by b; + +--echo # both should use range access +explain extended select a,b,c from t1 where a=1 and c=2 order by b limit 1000; +explain extended select a,b,c from t1 where a=1 and c=2 order by b limit 2000; +drop table t1,t0; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index e8421ad052a..5a67770c831 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -2420,12 +2420,16 @@ int SQL_SELECT::test_quick_select(THD *thd, key_map keys_to_use, records= head->stat_records(); if (!records) records++; /* purecov: inspected */ - scan_time= (double) records / TIME_FOR_COMPARE + 1; - read_time= (double) head->file->scan_time() + scan_time + 1.1; - if (head->force_index) + + if (head->force_index || force_quick_range) scan_time= read_time= DBL_MAX; - if (limit < records) - read_time= (double) records + scan_time + 1; // Force to use index + else + { + scan_time= (double) records / TIME_FOR_COMPARE + 1; + read_time= (double) head->file->scan_time() + scan_time + 1.1; + if (limit < records) + read_time= (double) records + scan_time + 1; // Force to use index + } possible_keys.clear_all();