LifeinAsia
msg:4534730 | 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 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
|
Dinkar
msg:4535483 | 2:07 pm on Jan 12, 2013 (gmt 0) |
A) - I would try this first.
B) - Isn't it better to use TRUNCATE TABLE?
|
LifeinAsia
msg:4535688 | 3:47 pm on Jan 13, 2013 (gmt 0) |
| B) - Isn't it better to use TRUNCATE TABLE? |
|
TRUNCATE TABLE removes ALL the data in the table. That negates the whole point of doing the update chunks at a time.
|
Dinkar
msg:4535782 | 12:00 am on Jan 14, 2013 (gmt 0) |
Yes, I know that it will remove all the data. I was thinking about DELETE and TRUNCATE and thought that it's better than DELETE.
But you are right, it negates the whole point of doing update in chunks.
|
Elric99
msg:4535871 | 10:04 am on Jan 14, 2013 (gmt 0) |
Thanks for the help.
I do have a column called productId and I had been using:
WHERE productID = 'AB1234' OR productID = 'AB2234' OR productID = 'AB3234'
I didn't know that you could use alphanumeric characters in BETWEEN
I've changed to:
WHERE productID BETWEEN 'AB1234' AND 'AB4567'
And this has shaved a great deal off the query time.
Thanks again
|
LifeinAsia
msg:4535946 | 5:09 pm on Jan 14, 2013 (gmt 0) |
| I didn't know that you could use alphanumeric characters in BETWEEN |
|
Just remember that it's doing a character by character comparison. (So 'AB1234' is actually LESS than 'AB125' even though 125 is less than 1234.)
And I assume you already have an index on productID?
|
piatkow
msg:4536305 | 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).
|
|
