Bug #6164 INSERT ... SELECT ... LIMIT creating too-large temporary tables
Submitted: 19 Oct 2004 20:10 Modified: 20 Oct 2004 17:20
Reporter: Dean Ellis Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:4.0.22 4.1.7 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[19 Oct 2004 20:10] Dean Ellis
Description:
INSERT ... SELECT ..., when source/destination table is the same table, copies entire table to temporary table, even with LIMIT 1, which makes the operation take too long and consume too much storage for larger tables.

ie: if table is 1GB, it will create a 1GB temporary table.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int );
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
INSERT INTO t1 SELECT t1.a FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6, t1 t7;
INSERT INTO t1 SELECT a FROM t1 LIMIT 1;

Suggested fix:
n/a
[19 Oct 2004 20:26] Dean Ellis
Same result with:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a int );
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
INSERT INTO t1 SELECT t1.a FROM t1, t1 t2, t1 t3, t1 t4, t1 t5, t1 t6, t1 t7;
INSERT INTO t1 SELECT SQL_BUFFER_RESULT * FROM t1 LIMIT 1;