MySQL Forums
Forum List  »  Performance

INSERT SELECT LIMIT very very slow
Posted by: Laurent Quérel
Date: February 07, 2005 08:05AM

I used an INSERT ... SELECT ... LIMIT to copy data between 2 tables.

INSERT IGNORE INTO table2 (col1, col2)
SELECT t1.col1, t2.col2
FROM table1 t1 LEFT JOIN table2 t2 ON t1.col1=t2.col1
WHERE t1.last_update >= 0
AND t1.col1 IS NOT NULL AND t2.col1 IS NULL LIMIT 1000

Volume and indexes :
Table 1 : 2 000 000 rows (index : col1 and last_update)
Table 2 : 1000 rows (index : col1)

I used the IGNORE option to deduplicate rows in table2 (and avoid DISTINCT or GROUP BY clause).

This query takes severals minutes to run. Why ?
In fact, if I used two separate queries (one for select and one for insert) the response is much quicker !

Can anyone explain the problem to me.


Laurent Quérel

Options: ReplyQuote

Written By
INSERT SELECT LIMIT very very slow
February 07, 2005 08:05AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.