From 644d9f38b97b198ec356aadfc390e0fef97c53f0 Mon Sep 17 00:00:00 2001 From: Varun Gupta Date: Tue, 21 Apr 2020 12:16:10 +0530 Subject: 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 --- sql/sql_select.cc | 21 +++++++++++++++------ 1 file changed, 15 insertions(+), 6 deletions(-) (limited to 'sql/sql_select.cc') 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; -- cgit v1.2.1