Msg#: 4534602 posted 6:20 pm on Jan 9, 2013 (gmt 0)
would it be better to remove the temporary table and update the actual table real-time?
No. If you do this and your connection breaks, you could be left with an empty mytable until you reconnect and complete the update.
which can take 3 hours
Which part is taking the longest? Just how much data are you updating?
I have tackled this issue in 2 different ways, depending on the situation: A) Rename mytable to mytable_old and mytable_temp to mytable
B) Instead of deleting all the data from mytable and copying from mytable_temp, do it in chunks. (This works only if there is a close relationship between the new/old data.) In other words, let's say there is a ProductID field: DELETE mytable WHERE ProductID BETWEEN 1 AND 10000
INSERT mytable SELECT * FROM mytable_temp WHERE ProductID BETWEEN 1 AND 10000
Msg#: 4534602 posted 9:33 pm on Jan 15, 2013 (gmt 0)
I don't know MYSQL as my DBA background was on big boxes but a classic way, if there are no referential integrity issues, is to create the temp table as identical to the original and then rename the pair of them.
If an update takes 3 hours then it sounds as if you need a faster connection or a faster box (or both).