Bug #9676 INSERT INTO x SELECT .. FROM x LIMIT 1; slows down with big tables
Submitted: 6 Apr 2005 13:57 Modified: 6 Jul 2006 21:02
Reporter: Alexander Keremidarski
Status: Closed
Category:Server: Optimizer Severity:S3 (Non-critical)
Version:4.1 OS:Any (any)
Assigned to: Georgi Kodinov Target Version:

[6 Apr 2005 13: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 17: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 15:10] Pekka Nousiainen 
Patch not ok, update status.
[15 Jun 2006 17: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 15: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 17: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 21:02] Paul DuBois 
Noted in 4.1.21, 5.0.24, 5.1.12 changelogs.
[13 Jul 2006 5:37] Paul DuBois 
5.0.x fix went to 5.0.25 instead.
[25 Jun 2007 23:49] Bugs System 
Pushed into 5.1.21-beta
[25 Jun 2007 23:51] Bugs System 
Pushed into 5.0.46