Bug #82173 x in (non-correlated subquery A) or x in (non-correlated subquery B) SLOW

• Submitted: 8 Jul 2016 20:53
• Modified: 3 Aug 2016 16:46
• Reporter: Moody Salem
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S4 (Feature request)
• Version: 5.7.13
• OS: Any
• CPU Architecture: Any
• Tags: IN, OR, subquery

[8 Jul 2016 20:53] Moody Salem

Description:
When doing the following query:

x in (non-correlated subquery) or x in (non-correlated subquery) SLOW: does a full table scan and seemingly runs the subqueries for each row?

vs.

x in (non-correlated subquery UNION non-correlated subquery) FAST: uses the index on x

How to repeat:
create table test_table_in_or (id int PRIMARY KEY, name VARCHAR(10));

insert into test_table_in_or 
(id, name)
values
(1, 'hello'),
(2, 'world'),
(3, 'test')
;

explain select id, name from test_table_in_or where id in (select id + 1 from test_table_in_or t where t.id < 1);

explain select id, name from test_table_in_or where id in (select id + 1 from test_table_in_or t where t.id < 1) or id in (select id from test_table_in_or tt where tt.id > 3);

Suggested fix:
This optimization should be done automatically

[11 Jul 2016 17:35] MySQL Verification Team

You should have made your example more clear by writing:

x in non-correlated query A OR x in no-correlated qeury B, but I have got your meaning.

This is fully justified feature request , which I am henceforth verifying it.

[3 Aug 2016 16:46] Moody Salem

Thanks Sinisa, I edited the title

Will this possibly be addresses in a minor version?