From 7e8a58020bc2b0dcac95937a0178401ecc55f6ad Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Mon, 20 Jan 2020 00:06:51 +0300 Subject: MDEV-21383: Possible range plan is not used under certain conditions [Variant 2 of the fix: collect the attached conditions] Problem: make_join_select() has a section of code which starts with "We plan to scan all rows. Check again if we should use an index." the code in that section will [unnecessarily] re-run the range optimizer using this condition: condition_attached_to_current_table AND current_table's_ON_expr Note that the original invocation of range optimizer in make_join_statistics was done using the whole select's WHERE condition. Taking the whole select's WHERE condition and using multiple-equalities allowed the range optimizer to infer more range restrictions. The fix: - Do range optimization using a condition that is an AND of this table's condition and all of the previous tables' conditions. - Also, fix the range optimizer to prefer SEL_ARGs with type=KEY_RANGE over SEL_ARGS with type=MAYBE_KEY, regardless of the key part. Computing key_and( SEL_ARG(type=MAYBE_KEY key_part=1), SEL_ARG(type=KEY_RANGE, key_part=2) ) will now produce the SEL_ARG with type=KEY_RANGE. --- sql/opt_range.cc | 2 ++ 1 file changed, 2 insertions(+) (limited to 'sql/opt_range.cc') diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 32e6a767f15..bd5f25d67c2 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -8999,6 +8999,8 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2, } if (key1->type == SEL_ARG::MAYBE_KEY) { + if (key2->type == SEL_ARG::KEY_RANGE) + return key2; key1->right= key1->left= &null_element; key1->next= key1->prev= 0; } -- cgit v1.2.1