Re: Bulk updates on live database

[Rick James]

Technically a split second; in reality, there would be some sluggishness because of caches.

Which to pick (MyISAM or InnoDB)? I don't think there is an easy answer.

* Query Cache -- all should be lost for that table (since it is a different table)
* MyISAM's index cache (key_buffer) -- don't know.
* MyISAM's data cache (the OS's file cache) -- a block is a block. There would be some thrashing while building the new table, and some reloading afterwards. OTOH, if there is room for both the old an new tables in cache, this is irrelevant.
* InnoDB's buffer pool (both data and index blocks) -- either thrashing (before and after RENAME) or no thrashing (if room for both).

Note: InnoDB storage is 2x-3x plumper than MyISAM, so that factors into the above comments.

Inserting into InnoDB -- Having the rows in PK order is optimal. Updating secondary index blocks still takes effort, possibly including random disk hits. If the table is 'big', this will slow to a crawl because of the disk thrashing. (Note: no thrashing if not secondary indexes.)

Inserting into MyISAM -- The order of the data does not matter. Having it in the order of some index(es) will help those indexes be built faster. If you have a huge table and often select multiple rows, it would be good to have those rows 'clustered' together.

Bottom line:
* Small table -- don't worry.
* Huge table -- you will suffer disk thrashing, but at least the switchover will be virtually instantaneous, with only some sluggishness on either side of the switchover.