enterprise-support team mailing list archive

[Sveta Smirnova <sveta@xxxxxxxxxxxxx>]

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