Bug #68748 Selects with large "IN" conditions are very slow
Submitted: 22 Mar 2013 16:26 Modified: 1 Apr 2013 16:14
Reporter: Thomas Lane Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.6.10 OS:Linux (Centos 5)
Assigned to:

[22 Mar 2013 16:26] Thomas Lane
Description:
SELECT queries with large "IN" conditions are incredibly slow in MySQL 5.6.10, and get exponentially slower with more values in the "IN" condition.
MySQL 4.0, 5.0, 5.5 did not have this problem.

We are using ISAM tables.

The metrics below are with a table with a single integer field, which is the primary key. The query selects some records using a large "IN" condition.

              v.5.6.10  v.5.5.30
 1000 values:   .02s       .01s
 2000 values:   .06s       .01s
 3000 values:   .13s       .01s
 4000 values:   .23s       .01s
 5000 values:   .35s       .01s
10000 values:  1.40s       .01s
20000 values:  6.79s       .01s
30000 values: 31.59s       .03s

Also, while the SELECT query is running, the table is locked.

How to repeat:
DROP TABLE IF EXISTS _Test_;
CREATE TABLE _Test_ (id INT NOT NULL PRIMARY KEY);
INSERT INTO _Test_ VALUES (0),(1),(2),(3),...,(29999),(30000);
SELECT COUNT(*) FROM _Test_ WHERE id IN (1,2,3,...,29999,30000);
[22 Mar 2013 16:28] Thomas Lane
Queries to illustrate performance problem

Attachment: test2.sql (text/x-sql), 610.86 KiB.

[25 Mar 2013 15:36] Thomas Lane
Here are some stored queries to illustrate the problem (so you don't have to copy/paste huge queries):

DROP TABLE IF EXISTS _Test_;
CREATE TABLE _Test_ (id INT NOT NULL PRIMARY KEY) ENGINE=MYISAM;
DROP PROCEDURE IF EXISTS _Populate_Test_;
DELIMITER $$
CREATE PROCEDURE _Populate_Test_(c INT)
BEGIN
  SET @values = '(1)';
  SET @c = 2;
  WHILE @c <= c DO
    SET @values = CONCAT(@values,',(',@c,')');
    SET @c = @c + 1;
  END WHILE;
  SET @q = CONCAT('INSERT INTO _Test_ (id) VALUES ',@values);
  PREPARE stmt FROM @q;
  EXECUTE stmt;
END
$$
DELIMITER ;

DROP PROCEDURE IF EXISTS _Run_Test_;
DELIMITER $$
CREATE PROCEDURE _Run_Test_(c INT)
BEGIN
  SET @values = '1';
  SET @c = 2;
  WHILE @c <= c DO
    SET @values = CONCAT(@values,',',@c);
    SET @c = @c + 1;
  END WHILE;
  SET @q = CONCAT('SELECT COUNT(*) FROM _Test_ WHERE id IN (',@values,')');
  PREPARE stmt FROM @q;
  EXECUTE stmt;
END
$$
DELIMITER ;

CALL _Populate_Test_(30000);

CALL _Run_Test_(1000);
CALL _Run_Test_(2000);
CALL _Run_Test_(3000);
CALL _Run_Test_(4000);
CALL _Run_Test_(5000);
CALL _Run_Test_(10000);
CALL _Run_Test_(20000);
CALL _Run_Test_(30000);
[1 Apr 2013 16:14] Shane Bester
Thank you for the testcase.  This bug is a duplicate of
http://bugs.mysql.com/bug.php?id=68046
[1 Apr 2013 16:17] Shane Bester
comparison to show it's fixed:

Latest code (5.6.12):
----------------------
mysql> CALL _Run_Test_(50000);
+----------+
| COUNT(*) |
+----------+
|    30000 |
+----------+
1 row in set (2.18 sec)

Query OK, 0 rows affected (2.18 sec)

-------------------

5.6.10:
------
mysql> CALL _Run_Test_(50000);
+----------+
| COUNT(*) |
+----------+
|    30000 |
+----------+
1 row in set (37.72 sec)

Query OK, 0 rows affected (37.72 sec)