Bug #95200 Ref joins could use composite index key parts for > ,<, between
Submitted: 30 Apr 2019 9:13 Modified: 2 May 2019 17:32
Reporter: Arnaud Adant Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:8.0.15 OS:Any
Assigned to: CPU Architecture:Any
Tags: Optimizer

[30 Apr 2019 9:13] Arnaud Adant
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.
[1 May 2019 9:09] Arnaud Adant
Maybe this can be seen as a bug ? Asking for trouble with Sinisa :-)
[2 May 2019 14:01] MySQL Verification Team
Hi Arnaud,

My dear friend, how are you ???

I find that your feature request is quite interesting and that is why I am verifying it.

So, no troubles with me ......... ;-)

Verified as a feature request.
[2 May 2019 17:32] Arnaud Adant
Thank you Sinisa !
[3 May 2019 12:38] MySQL Verification Team
Arnaud, mon ami,

There is nothing for you to thank me for ....