Re: UPDATE 6m records quickly

[James Day]

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.