Description:
The manual only mentions = and <=> operators for join type = ref :
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#jointype_ref
It would be possible to use index key parts when they are discriminant.
There is an index on (id2, valid_to) and it is very discriminant.
This is fast :
MySQL [test]> select count(*) from t straight_join t t2 on t.id2 = t2.id2 and t2.valid_to ='9999-12-31';
+----------+
| count(*) |
+----------+
| 262148 |
+----------+
1 row in set (0.33 sec)
MySQL [test]> show session status like 'Handler%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| Handler_commit | 3 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 24 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 4 |
| Handler_read_key | 262166 |
| Handler_read_last | 0 |
| Handler_read_next | 524299 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 8 |
| Handler_read_rnd_next | 18 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 8 |
+----------------------------+--------+
18 rows in set (0.00 sec)
This is much slower :
MySQL [test]> select count(*) from t straight_join t t2 on t.id2 = t2.id2 and t2.valid_to >='9999-12-31';
+----------+
| count(*) |
+----------+
| 262148 |
+----------+
1 row in set (12.89 sec)
MySQL [test]> show session status like 'Handler%';
+----------------------------+----------+
| Variable_name | Value |
+----------------------------+----------+
| Handler_commit | 3 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 24 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 4 |
| Handler_read_key | 262166 |
| Handler_read_last | 0 |
| Handler_read_next | 69203945 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 8 |
| Handler_read_rnd_next | 18 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 8 |
+----------------------------+----------+
18 rows in set (0.00 sec)
How to repeat:
drop table if exists t;
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;
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 straight_join t t2 on t.id2 = t2.id2 and t2.valid_to >='9999-12-31';
show session status like 'Handler%';
Suggested fix:
Use the B-tree to perform ref / range join access on keys with operations other than = and <=> when they are discriminant.