Join performance on composite secondary index lookup with inequality

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';

select count(*) from t join t t2 on t.id2 = t2.id2 and t2.valid_to ='9999-12-31';