revision-id: f72361e07346d142c4f3dda482b441c086c626f2 (mariadb-10.0.30-372-gf72361e0734) parent(s): 3a724800eebf78bd179bd1a2637ebe0a175e84d6 author: Varun Gupta committer: Varun Gupta timestamp: 2018-06-10 02:16:12 +0530 message: MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY The issue in this case is that we take in account the estimates from quick keys instead of rec_per_key. The estimates for quick keys are better than rec_per_key only if we have ref(const), so we need to check that all keyparts in the ref key are of the type ref(const). Also we need to make sure that the # of keyparts in ref and quick keys are same. --- mysql-test/r/order_by_innodb.result | 48 +++++++++++++++++++++++++++++++++++++ mysql-test/t/order_by_innodb.test | 34 +++++++++++++++++++++++++- sql/sql_select.cc | 18 +++++++++++++- 3 files changed, 98 insertions(+), 2 deletions(-) diff --git a/mysql-test/r/order_by_innodb.result b/mysql-test/r/order_by_innodb.result index 3c6c4053741..d98e89c730a 100644 --- a/mysql-test/r/order_by_innodb.result +++ b/mysql-test/r/order_by_innodb.result @@ -11,3 +11,51 @@ a b c d 8 NULL 9 NULL 8 NULL 10 NULL DROP TABLE t1; +# +# MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY +# +create table t1(a int) engine=innodb; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2( +id int primary key, +key1 int, +col1 int, +key(key1)) engine=innodb; +insert into t2 select A.a + B.a*10 + C.a*100 + D.a* 1000,A.a + 10*B.a, 123456 +from t1 A, t1 B, t1 C, t1 D; +alter table t2 add key2 int; +update t2 set key2=key1; +alter table t2 add key(key2); +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain select +(SELECT +concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL # +2 DEPENDENT SUBQUERY t2 ref key1 key1 5 test.t1.a # Using index condition; Using where; Using filesort +select(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +9900-0-123456 +9901-1-123456 +9902-2-123456 +9903-3-123456 +9904-4-123456 +9905-5-123456 +9906-6-123456 +9907-7-123456 +9908-8-123456 +9909-9-123456 +drop table t1,t2; diff --git a/mysql-test/t/order_by_innodb.test b/mysql-test/t/order_by_innodb.test index c20eaceb053..3dad752b16b 100644 --- a/mysql-test/t/order_by_innodb.test +++ b/mysql-test/t/order_by_innodb.test @@ -18,6 +18,38 @@ INSERT INTO t1 (a,c) VALUES (20, 22),(20, 24),(20, 25),(20, 26),(20, 27),(20, 28); SELECT * FROM t1 WHERE a = 8 AND (b = 1 OR b IS NULL) ORDER BY c; - DROP TABLE t1; +--echo # +--echo # MDEV-16214: Incorrect plan taken by the optimizer , uses INDEX instead of ref access with ORDER BY +--echo # + +create table t1(a int) engine=innodb; +insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t2( +id int primary key, +key1 int, +col1 int, +key(key1)) engine=innodb; +insert into t2 select A.a + B.a*10 + C.a*100 + D.a* 1000,A.a + 10*B.a, 123456 +from t1 A, t1 B, t1 C, t1 D; + +alter table t2 add key2 int; +update t2 set key2=key1; +alter table t2 add key(key2); +analyze table t2; + +--replace_column 9 # +explain select +(SELECT +concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +select(SELECT concat(id, '-', key1, '-', col1) +FROM t2 +WHERE t2.key1 = t1.a and t2.key1 IS NOT NULL +ORDER BY t2.key2 ASC LIMIT 1) +from t1; +drop table t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f8435eca995..c6ab451501d 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -25117,7 +25117,23 @@ test_if_cheaper_ordering(const JOIN_TAB *tab, ORDER *order, TABLE *table, */ if (ref_key >= 0 && tab->type == JT_REF) { - if (table->quick_keys.is_set(ref_key)) + bool all_ref_parts_const= TRUE; + /* + For all the parts of the ref key we check if all of them belong + to the type ref(const). This is done because if all parts of the ref + key are of type ref(const), then we are sure that the estimates + provides by quick keys is better than that provide by rec_per_key. + */ + for (uint key_part=0; key_part < tab->ref.key_parts; key_part++) + { + if (!(tab->ref.const_ref_part_map & (key_part_map(1) << key_part))) + { + all_ref_parts_const= FALSE; + break; + } + } + if (all_ref_parts_const && table->quick_keys.is_set(ref_key) && + table->quick_key_parts[ref_key] == tab->ref.key_parts) refkey_rows_estimate= table->quick_rows[ref_key]; else {