MySQL Forums
Forum List  »  Performance

Re: UPDATE 6m records quickly
Posted by: James Day
Date: January 24, 2005 09:43PM

If you're ordering in that way you've already achieved all the benefit you're likely to get from MyISAM cache efficiency. Next step, are there indexes on the fields being updated? If yes, have you tried ALTER TABLE ... DISABLE KEY for those keys, followed by an enable at the end of the changes? That can be substantialy faster (though it can also make a server unresponsive for a while sometimes, so you'd want to use care if doing it on a live production server).

If you're retrieving the original records to construct your update statements, doing a chunk of retrievals then a chunk of updates can help caching - the retrievals to get the data you need to update will load the data into RAM. Then work through the table one chunk at a time.

You can try using the delay-key-write option to defer the writes of changed key pages to disk.

You could try lock table around batches/chunks of updates, to see if that helps.

You could consider LOAD DATA INFILE for the primary key and new value for one field, then an update query to merge the loaded and existing data.

When changing every record, you might also want to compare the speed of LOAD DATA INFILE for the modified data into a different table, then a pair of table renames to swap active table.

Chunking was to try to make sure that you were updating record pages which were in RAM already. Could wonder whether there is a large enough key_buffer_size to hold all of the index pages in RAM.

You could try InnoDB to see if it is faster for this job. It may be and/or it may be faster anyway if you are normally retrieving in primary key order, because it'll add new records in primary key order.

Options: ReplyQuote

Written By
January 23, 2005 03:47PM
January 24, 2005 01:04AM
January 24, 2005 03:51PM
January 24, 2005 03:53PM
Re: UPDATE 6m records quickly
January 24, 2005 09:43PM
January 25, 2005 12:47AM
January 25, 2005 02:41PM
January 25, 2005 11:32PM
January 26, 2005 03:41AM

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.