MySQL Bugs: #9676: INSERT INTO x SELECT .. FROM x LIMIT 1; slows down with big tables

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

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;

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

Patch not ok, update status.

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

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.

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

Noted in 4.1.21, 5.0.24, 5.1.12 changelogs.

5.0.x fix went to 5.0.25 instead.

Pushed into 5.1.21-beta

Pushed into 5.0.46