Bug #109170 Setting index_merge_sort_union=off also disables other index merge plans
Submitted: 22 Nov 2022 19:45 Modified: 23 Nov 2022 4:38
Reporter: Manuel Ung Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any

[22 Nov 2022 19:45] Manuel Ung
Description:
Setting index_merge_sort_union=off seems to disable other index merge plans, even though the intention is to only disable sort_union index merge plans.

How to repeat:
create table t (i int, j int, k int, key(i), key(j, k), key(j));
insert into t (WITH RECURSIVE a (i) AS ( SELECT 0  union all SELECT i+1  from a where i < 9  ), b (i) AS  (SELECT x.i + y.i * 10 + z.i * 100 + w.i * 1000 +  v.i * 10000 FROM a x, a y, a z, a w, a v) SELECT b.i, b.i, b.i from b order by i);
explain select * from t where i = 0 or j = 2;
set optimizer_switch='index_merge_sort_union=off';
explain select * from t where i = 0 or j = 2;
explain select /*+ INDEX_MERGE(t i,j_2) */ * from t where i = 0 or j = 2;
drop table t;

The 3 explains give the following output:
explain select * from t where i = 0 or j = 2;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t       NULL    index_merge     i,j,j_2 i,j     5,5     NULL    2       100.00  Using sort_union(i,j); Using where

set optimizer_switch='index_merge_sort_union=off';
explain select * from t where i = 0 or j = 2;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t       NULL    ALL     i,j,j_2 NULL    NULL    NULL    100000  0.00    Using where

explain select /*+ INDEX_MERGE(t i,j_2) */ * from t where i = 0 or j = 2;
id      select_type     table   partitions      type    possible_keys   key     key_len ref     rows    filtered        Extra
1       SIMPLE  t       NULL    index_merge     i,j,j_2 i,j_2   5,5     NULL    2       100.00  Using union(i,j_2); Using where

You can see that after turning off index_merge_sort_union=off, we fall back to full table scan. However, I would expect it to fallback to a union plan instead, since index_merge_union is on.  If I change the query to use optimizer hints, then you can see that the union plan is possible (and chosen).
[23 Nov 2022 4:38] MySQL Verification Team
Hello Manuel Ung,

Thank you for the report and feedback.

regards,
Umesh