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
• Status: Closed
• 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] MySQL Verification Team

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] MySQL Verification Team

5.6.5 is fast. 5.6.6+  is slow....

[13 Jan 2013 13:09] MySQL Verification Team

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] MySQL Verification Team

Bug #68818 was a duplicate

[1 Apr 2013 16:16] MySQL Verification Team

bug #68748 was a duplicate of this