| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
| Version: | 5.6.10 | OS: | Linux (Centos 5) |
| Assigned to: | CPU Architecture: | Any | |
[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]
MySQL Verification Team
Thank you for the testcase. This bug is a duplicate of http://bugs.mysql.com/bug.php?id=68046
[1 Apr 2013 16:17]
MySQL Verification Team
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)
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);