diff options
| -rw-r--r-- | mysql-test/r/group_min_max.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/order_by_innodb.result | 25 | ||||
| -rw-r--r-- | mysql-test/suite/maria/icp.result | 2 | ||||
| -rw-r--r-- | mysql-test/t/order_by_innodb.test | 24 | ||||
| -rw-r--r-- | sql/opt_range.cc | 14 | ||||
| -rw-r--r-- | storage/tokudb/mysql-test/tokudb_bugs/r/2970.result | 2 |
6 files changed, 62 insertions, 9 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..10c041c0c28 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -48,3 +48,28 @@ 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), +b int(11), +c int(11), +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 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 Extra +1 SIMPLE t1 ref a_c,a_b a_c 10 const,const 20 Using where; Using filesort +# both should use range access +explain 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 Extra +1 SIMPLE t1 range a_c,a_b a_b 5 NULL 200 Using where +explain 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 Extra +1 SIMPLE t1 range a_c,a_b a_b 5 NULL 200 Using where +drop table t1,t0; diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index e37877c8527..49eaed9dd6d 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -167,7 +167,7 @@ WHERE ts BETWEEN '0000-00-00' AND '2010-00-01 00:00:00' ORDER BY ts DESC LIMIT 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 index PRIMARY PRIMARY 4 NULL 2 Using where +1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where DROP TABLE t1; # diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index 097eddd24f1..10769367c39 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), +b int(11), +c int(11), +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 select a,b,c from t1 where a=1 and c=2 order by b; + +--echo # both should use range access +explain select a,b,c from t1 where a=1 and c=2 order by b limit 1000; +explain 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 45dad8882a1..7e987c3cfba 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(); diff --git a/storage/tokudb/mysql-test/tokudb_bugs/r/2970.result b/storage/tokudb/mysql-test/tokudb_bugs/r/2970.result index 83ba8821f27..c322865b09d 100644 --- a/storage/tokudb/mysql-test/tokudb_bugs/r/2970.result +++ b/storage/tokudb/mysql-test/tokudb_bugs/r/2970.result @@ -6,5 +6,5 @@ create table t2970 (a int, b int, c int, d int, key(a), key(a,b)); insert into t2970 values (1,1,1,1),(1,2,3,4); explain select a,count(b),max(b) from t2970 where a > 0 group by a order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t2970 index a,a_2 a_2 10 NULL 2 Using where; Using index +1 SIMPLE t2970 range a,a_2 a_2 5 NULL 2 Using where; Using index drop table t2970; |