Bug #9676 INSERT INTO x SELECT .. FROM x LIMIT 1; slows down with big tables
Submitted: 6 Apr 2005 11:57 Modified: 6 Jul 2006 19:02
Reporter: Alexander Keremidarski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1 OS:Any (any)
Assigned to: Georgi Kodinov CPU Architecture:Any

[6 Apr 2005 11:57] Alexander Keremidarski
Description:
When doing INSERT ... SELECT ... LIMIT 1 without ORDER BY on same Cluster table the speed of the insert is directly related to the number of rows.

Inserting 1 row is  very fast as well as SELECT .. LIMIT 1;

When doing the same insert from another table it is also fast.

When inserting from same table it slows down as if Cluster retrieves all the rows first instead of picking first possible only.

Both MyISAM and InnoDB does not show such behavior.

How to repeat:
 CREATE TABLE `t1` (
  `id` int(11) NOT NULL auto_increment,
  `x` int(11) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=ndbcluster;

INSERT INTO t1 (x) SELECT 1;
Query OK, 1 row affected (0.01 sec)

INSERT INTO t1 (x) SELECT 1 FROM t1;
Query OK, 1 row affected (0.02 sec)

-- repeat the above several times so there are many thousand rows in the table
SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|    32768 |
+----------+

INSERT INTO t1 (x) SELECT 1 FROM t1 LIMIT 1;
Query OK, 1 row affected (0.21 sec)

-- compare to
INSERT INTO t1 (x) SELECT 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

-- Insert more rows
SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|    65540 |
+----------+
1 row in set (0.00 sec)

INSERT INTO t1 (x) SELECT 1 FROM t1 LIMIT 1;
Query OK, 1 row affected (0.39 sec)
Records: 1  Duplicates: 0  Warnings: 0

INSERT INTO t1 (x) SELECT 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

-- With twice more rows INSERT .. SELECT .. is about twice slower while inserting single row 
-- itself remains fast as well as insert from another table

INSERT INTO t1 (x) SELECT 1 FROM t2 LIMIT 1;
Query OK, 1 row affected (0.05 sec)

SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
|    65526 |
+----------+

-- t2 is created as CREATE TABLE t2 LIKE t1;
[13 Apr 2005 15:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/23970
[18 May 2005 13:10] Pekka Nousiainen
Patch not ok, update status.
[15 Jun 2006 15:23] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7710
[21 Jun 2006 13:35] Georgi Kodinov
The problem was that when filling up the temp table in statements like :
insert into t1(x) select 1 from t1 limit 1;
the temp table was getting all the rows of t1. 
Corrected to store no more than LIMIT rows (if applicable) into the temp table.
[6 Jul 2006 15:34] Evgeny Potemkin
Currently in INSERT ... SELECT ... LIMIT ... the compiler uses a 
temporary table to store the results of SELECT ... LIMIT .. and then
uses that table as a source for INSERT. The problem is that in some cases
it actually skips the LIMIT clause in doing that and materializes the 
whole SELECT result set regardless of the LIMIT.

Fixed in 4.1.21, 5.0.24, 5.1.12
[6 Jul 2006 19:02] Paul DuBois
Noted in 4.1.21, 5.0.24, 5.1.12 changelogs.
[13 Jul 2006 3:37] Paul DuBois
5.0.x fix went to 5.0.25 instead.
[25 Jun 2007 21:49] Bugs System
Pushed into 5.1.21-beta
[25 Jun 2007 21:51] Bugs System
Pushed into 5.0.46