Public bug reported:
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
** Affects: mysql-server
Importance: Unknown
Status: Unknown
** Affects: percona-server
Importance: Medium
Status: Confirmed
** Affects: percona-server/5.6
Importance: Medium
Status: Confirmed
** Affects: percona-server/5.7
Importance: Medium
Status: Confirmed
** Tags: i69109
** Attachment added: "Test case for MTR"
https://bugs.launchpad.net/bugs/1582919/+attachment/4664939/+files/bug81469.test
** Bug watch added: MySQL Bug System #81469
http://bugs.mysql.com/bug.php?id=81469
** Also affects: mysql-server via
http://bugs.mysql.com/bug.php?id=81469
Importance: Unknown
Status: Unknown
** Also affects: percona-server/5.6
Importance: Undecided
Status: New
** Also affects: percona-server/5.7
Importance: Undecided
Status: Confirmed
** Changed in: percona-server/5.6
Status: New => Incomplete
** Changed in: percona-server/5.6
Status: Incomplete => Confirmed
** Changed in: percona-server/5.6
Importance: Undecided => Medium
** Changed in: percona-server/5.7
Importance: Undecided => Medium
--
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1582919
Title:
Slow performance for IN subquery and UNION
To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1582919/+subscriptions
--
Mailing list: https://launchpad.net/~enterprise-support
Post to : [email protected]
Unsubscribe : https://launchpad.net/~enterprise-support
More help : https://help.launchpad.net/ListHelp
