Bug #81469 Slow performance for IN subquery and UNION
Submitted: 17 May 2016 21:51 Modified: 18 May 2016 6:53
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.30, 5.7.12 OS:Any
Assigned to: CPU Architecture:Any

[17 May 2016 21:51] Sveta Smirnova
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
[17 May 2016 21:51] Sveta Smirnova
Test case for MTR

Attachment: bug81469.test (application/octet-stream, text), 71.96 KiB.

[17 May 2016 21:54] Sveta Smirnova
Percona bug report: https://bugs.launchpad.net/percona-server/+bug/1582919
[18 May 2016 6:53] Umesh Shastry
Hello Sveta,

Thank you for the report and test case.
Verified as described with 5.6.30 build.

Thanks,
Umesh