Bug #109764 Index merge not considered for indexes that have a common prefix

• Submitted: 24 Jan 2023 20:56
• Modified: 25 Jan 2023 6:42
• Reporter: Manuel Ung
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 8.0.32
• OS: Any
• CPU Architecture: Any

[24 Jan 2023 20:56] Manuel Ung

Description:
This is a feature request for index merge to consider merging indexes that share a common prefix. See example below:

How to repeat:
# index merge not considered
create table t (i int, j int, k int, primary key(i, j),  key(i, k)) engine=innodb;
explain
select /*+ INDEX_MERGE(t PRIMARY,i) */ * from t where i = 0 and (j = 1 or k = 2);
drop table t;

# index merge considered
create table t (i int, j int, k int, primary key(j, i),  key(k, i)) engine=innodb;
explain
select /*+ INDEX_MERGE(t PRIMARY,k) */ * from t where i = 0 and (j = 1 or k = 2);
drop table t;

Suggested fix:
I haven't thought through the implementation completely, but probably would need to change tree_or to perform index merge even if sel_trees_can_be_ored returns true.

[25 Jan 2023 6:42] MySQL Verification Team

Hello Manuel Ung,

Thank you for the feature request.

regards,
Umesh