Description:
If I use IN subquery which contains UNION of 3 integers performance is slower than if I wrap same subquery in another select.
E.g.
select id from events where id in (select 60241922 union all select 60241923 union all select 60241924); - slow query
select id from events where id in (select * from (select 60241922 union all select 60241923 union all select 60241924) v); - fast query
How to repeat:
In 5.7.12:
flush status;
select now(6);
now(6)
2016-05-18 00:33:11.453692
select id from events where id in (select 60241922 union all select 60241923 union all select 60241924);
id
60241922
60241923
60241924
select now(6);
now(6)
2016-05-18 00:33:11.458543
show status like 'Handler%';
Variable_name Value
Handler_commit 1
Handler_delete 0
Handler_discover 0
Handler_external_lock 2
Handler_mrr_init 0
Handler_prepare 0
Handler_read_first 1
Handler_read_key 1
Handler_read_last 0
Handler_read_next 510
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 0
flush status;
select now(6);
now(6)
2016-05-18 00:33:11.459594
select id from events where id in (select * from (select 60241922 union all select 60241923 union all select 60241924) v);
id
60241922
60241923
60241924
select now(6);
now(6)
2016-05-18 00:33:11.460421
show status like 'Handler%';
Variable_name Value
Handler_commit 1
Handler_delete 0
Handler_discover 0
Handler_external_lock 2
Handler_mrr_init 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 3
Handler_read_last 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 8
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 6
Full test case will be attached soon.
Suggested fix:
Optimize first query same way as second one
