Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.1, 10.1.38, 10.3.13, 10.2, 10.3
-
None
Description
Joins with ids and dates are very common especially with temporal tables.
Looks like MariaDB optimizer only uses the id and not the date for the lookup leading to poor join performance when the date is discriminant.
Here is the test case.
create table t( |
id int not null auto_increment primary key, |
id2 int not null, |
valid_to datetime(6) not null, |
index id2_valid_to (id2, valid_to) |
) engine=InnoDB;
|
|
truncate table t; |
|
insert into t(id2, valid_to) values(rand()*1000, date_add(now(), interval - rand()*1000 day)); |
insert into t(id2, valid_to) values(rand()*1000, date_add(now(), interval - rand()*1000 day)); |
insert into t(id2, valid_to) values(rand()*1000, date_add(now(), interval - rand()*1000 day)); |
insert into t(id2, valid_to) values(rand()*1000, date_add(now(), interval - rand()*1000 day)); |
|
replace into t(id2, valid_to) select rand()*1000, date_add(now(), interval - rand()*1000 day) from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9; |
|
drop temporary table if exists t_max; |
create temporary table t_max as select max(id) id from t group by id2; |
update t, t_max set t.valid_to='9999-12-31' where t.id = t_max.id; |
commit; |
analyze table t; |
select count(*) from t join t t2 on t.id2 = t2.id2 and t2.valid_to >='9999-12-31'; |
MySQL 8.0 is fast, MariaDB 10.1.29, 10.1.38, 10.3.13 are slow.
select count(*) from t join t t2 on t.id2 = t2.id2 and t2.valid_to ='9999-12-31'; |
is fast