INSERT SELECT LIMIT very very slow
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.
Thanks
Laurent Quérel
Subject
Views
Written By
Posted
INSERT SELECT LIMIT very very slow
4370
February 07, 2005 08:05AM
2251
February 09, 2005 12:16AM
2105
February 21, 2005 07:26AM
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.