revision-id: 83b65d6445932b6465bfe81228dfff0324ea50e4 (mariadb-10.3.21-85-g83b65d64459) parent(s): 42af2b1d8b8a8508c5aac5c2e0bcf4554c5200ce author: Varun Gupta committer: Varun Gupta timestamp: 2020-04-21 14:11:29 +0530 message: MDEV-21480: Unique key using ref access though eq_ref access can be used For a unique key if all the keyparts are NOT NULL or the predicates involving the keyparts is NULL rejecting, then we can use EQ_REF access instead of ref access with the unique key --- mysql-test/main/invisible_field.result | 2 +- mysql-test/main/join.result | 4 +-- mysql-test/main/join.test | 2 +- mysql-test/main/key.result | 42 +++++++++++++++++++++++++++ mysql-test/main/key.test | 21 ++++++++++++++ mysql-test/main/order_by.result | 2 +- mysql-test/main/subselect_sj_nonmerged.result | 4 +-- mysql-test/main/table_elim.result | 2 +- sql/sql_select.cc | 21 ++++++++++---- 9 files changed, 86 insertions(+), 14 deletions(-) diff --git a/mysql-test/main/invisible_field.result b/mysql-test/main/invisible_field.result index 9cdc54f2e43..053a9fd8e50 100644 --- a/mysql-test/main/invisible_field.result +++ b/mysql-test/main/invisible_field.result @@ -405,7 +405,7 @@ c int(11) YES NULL explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where -1 SIMPLE t1 ref b,c b 5 test.t2.c 1 Using where +1 SIMPLE t1 eq_ref b,c b 5 test.t2.c 1 Using where select * from t1,t2 where t1.b = t2.c and t1.c = t2.b; a a b c 1 1 1 1 diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index fe6d18f7807..e667eab7eb9 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -3333,11 +3333,11 @@ show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment t1 0 PRIMARY 1 pk A 1010 NULL NULL BTREE t1 0 a 1 a A 10 NULL NULL YES BTREE -# t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45): +# t1 must use eq_ref(t1.a=t0.a) and rows must be 1 (and not 45): explain select * from t0,t1 where t0.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where -1 SIMPLE t1 ref a a 5 test.t0.a 1 +1 SIMPLE t1 eq_ref a a 5 test.t0.a 1 drop table t0,t1; # # MDEV-21383: Possible range plan is not used under certain conditions diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test index c72ff0e1a8c..07c72827d25 100644 --- a/mysql-test/main/join.test +++ b/mysql-test/main/join.test @@ -1744,7 +1744,7 @@ analyze table t1; set myisam_stats_method=@tmp1; show keys from t1; ---echo # t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45): +--echo # t1 must use eq_ref(t1.a=t0.a) and rows must be 1 (and not 45): explain select * from t0,t1 where t0.a=t1.a; drop table t0,t1; diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result index ba1124aaa14..6edb0404524 100644 --- a/mysql-test/main/key.result +++ b/mysql-test/main/key.result @@ -639,3 +639,45 @@ SHOW STATUS LIKE 'Last_query_cost'; Variable_name Value Last_query_cost 14.199000 DROP TABLE t1; +# +# MDEV-21480: Unique key using ref access though eq_ref access can be used +# +create table t1(a int, b int,c int, primary key(a), unique key(b,c)); +insert into t1 select seq, seq, seq from seq_1_to_10; +create table t2(a int, b int,c int); +insert into t2 select seq, seq, seq+1 from seq_1_to_100; +EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE t1 eq_ref b b 10 test.t2.a,test.t2.b 1 Using index +SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; +c c +1 2 +2 3 +3 4 +4 5 +5 6 +6 7 +7 8 +8 9 +9 10 +10 11 +alter table t1 drop PRIMARY KEY; +alter table t1 add PRIMARY KEY(b,c); +EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where +1 SIMPLE t1 eq_ref PRIMARY,b PRIMARY 8 test.t2.a,test.t2.b 1 Using index +SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; +c c +1 2 +2 3 +3 4 +4 5 +5 6 +6 7 +7 8 +8 9 +9 10 +10 11 +drop table t1,t2; diff --git a/mysql-test/main/key.test b/mysql-test/main/key.test index 0695dc2eecb..b386f1298fd 100644 --- a/mysql-test/main/key.test +++ b/mysql-test/main/key.test @@ -1,6 +1,7 @@ --disable_warnings drop table if exists t1,t2,t3; --enable_warnings +--source include/have_sequence.inc SET SQL_WARNINGS=1; # @@ -581,3 +582,23 @@ EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a; SHOW STATUS LIKE 'Last_query_cost'; DROP TABLE t1; + +--echo # +--echo # MDEV-21480: Unique key using ref access though eq_ref access can be used +--echo # + +create table t1(a int, b int,c int, primary key(a), unique key(b,c)); +insert into t1 select seq, seq, seq from seq_1_to_10; + +create table t2(a int, b int,c int); +insert into t2 select seq, seq, seq+1 from seq_1_to_100; + +EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; +SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; + +alter table t1 drop PRIMARY KEY; +alter table t1 add PRIMARY KEY(b,c); +EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; +SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b; + +drop table t1,t2; diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result index ec03e8c5f45..1bb597095db 100644 --- a/mysql-test/main/order_by.result +++ b/mysql-test/main/order_by.result @@ -1515,7 +1515,7 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range a,b,c c 5 NULL 420 Using where -1 SIMPLE t1 ref a a 39 test.t2.a,const 1 Using where; Using index +1 SIMPLE t1 eq_ref a a 39 test.t2.a,const 1 Using where; Using index SELECT d FROM t3 AS t1, t2 AS t2 WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE' ORDER BY t2.c LIMIT 1; diff --git a/mysql-test/main/subselect_sj_nonmerged.result b/mysql-test/main/subselect_sj_nonmerged.result index 4d9a70e6bba..7b73ae0fc57 100644 --- a/mysql-test/main/subselect_sj_nonmerged.result +++ b/mysql-test/main/subselect_sj_nonmerged.result @@ -69,7 +69,7 @@ t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) -1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),test.t0.a 1 +1 PRIMARY t4 eq_ref a a 10 <subquery2>.max(t2.a),test.t0.a 1 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B; @@ -79,7 +79,7 @@ t4.b in (select max(t2.a) from t1, t2 group by t2.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join) -1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 1 +1 PRIMARY t4 eq_ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 1 3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join) 2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result index 7780e165451..d12b6470ecf 100644 --- a/mysql-test/main/table_elim.result +++ b/mysql-test/main/table_elim.result @@ -279,7 +279,7 @@ insert into t2 values explain select t1.* from t1 left join t2 on t2.a=t1.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index -1 SIMPLE t2 ref a a 3 test.t1.a 1 Using where +1 SIMPLE t2 eq_ref a a 3 test.t1.a 1 Using where drop table t1, t2; # # check UPDATE/DELETE that look like they could be eliminated diff --git a/sql/sql_select.cc b/sql/sql_select.cc index d6c4a49526c..de66f8bc59e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -10066,6 +10066,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, uchar *key_buff=j->ref.key_buff, *null_ref_key= 0; uint null_ref_part= NO_REF_PART; bool keyuse_uses_no_tables= TRUE; + uint not_null_keyparts= 0; if (ftkey) { j->ref.items[0]=((Item_func*)(keyuse->val))->key_item(); @@ -10095,6 +10096,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, j->ref.items[i]=keyuse->val; // Save for cond removal j->ref.cond_guards[i]= keyuse->cond_guard; + if (!keyuse->val->maybe_null || keyuse->null_rejecting) + not_null_keyparts++; /* Set ref.null_rejecting to true only if we are going to inject a "keyuse->val IS NOT NULL" predicate. @@ -10154,12 +10157,18 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, ulong key_flags= j->table->actual_key_flags(keyinfo); if (j->type == JT_CONST) j->table->const_table= 1; - else if (!((keyparts == keyinfo->user_defined_key_parts && - ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)) || - (keyparts > keyinfo->user_defined_key_parts && // true only for extended keys - MY_TEST(key_flags & HA_EXT_NOSAME) && - keyparts == keyinfo->ext_key_parts)) || - null_ref_key) + else if (!((keyparts == keyinfo->user_defined_key_parts && + ( + (key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME || + /* Unique key and all keyparts are NULL rejecting */ + ((key_flags & HA_NOSAME) && keyparts == not_null_keyparts) + )) || + /* true only for extended keys */ + (keyparts > keyinfo->user_defined_key_parts && + MY_TEST(key_flags & HA_EXT_NOSAME) && + keyparts == keyinfo->ext_key_parts) + ) || + null_ref_key) { /* Must read with repeat */ j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;