Bug #68046 IN() clause with many constant values does not perform
Submitted: 7 Jan 2013 9:29 Modified: 22 Jan 2013 14:04
Reporter: Martijn Otto Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6-rc9 OS:Any (Ubuntu 12.04)
Assigned to: Jørgen Løland CPU Architecture:Any

[7 Jan 2013 9:29] Martijn Otto
Description:
When using a query that has, say, 30000 constant items in an IN() clause, this query performs terribly, terribly slow on mysql 5.6 (as compared to 5.5).

The following query takes around 10 seconds on a 5.6 server, while it takes around .07 seconds. This difference goes up as the number of items increases. With around 1.000.000 items, the query takes around 27.000 seconds (around nine hours) on 5.6, while taking around 45 seconds on a 5.5 server.

Note that the actual items in the IN() clause are not attached to this bug-report (as the instructions would get too big). Also note, that once started, the query will take several hours to kill on a 5.6 server.

How to repeat:
CREATE TABLE intest (id integer not null primary key);
INSERT INTO intest (id) VALUES (1),(2);
SELECT id FROM intest WHERE id IN (1 ... 30000);
[7 Jan 2013 9:46] Shane Bester
testcase. 5.5.29 takes < 0.1 seconds, 5.6.10 takes > 4 seconds.

Attachment: bug68046.sql (application/octet-stream, text), 194.98 KiB.

[7 Jan 2013 9:59] Shane Bester
5.6.5 is fast. 5.6.6+  is slow....
[13 Jan 2013 13:09] Shane Bester
The fix for http://bugs.mysql.com/bug.php?id=58731 introduced this bug.

Code that was previously only run only in debug builds (function: test_rb_tree) was now running in optimized builds by mistake.
[22 Jan 2013 14:04] Paul Dubois
Noted in 5.6.11, 5.7.1 changelogs.

Queries with many values in a IN() clause were slow due to inclusion
of debugging code in non-debugging builds.
[1 Apr 2013 14:37] Shane Bester
Bug #68818 was a duplicate
[1 Apr 2013 16:16] Shane Bester
bug #68748 was a duplicate of this