when to use OPTIMIZE in mysql

Question

I have a database full of time-sensitive data, so on a daily basis I truncate the table and then import the new data (from a merge of other databases) into the truncated table.

Currently I am running OPTIMIZE on the table after I have imported the daily refresh of data. However, looking at the mysql OPTIMIZE syntax page http://dev.mysql.com/doc/refman/5.1/en/optimize-table.html

it says I can optimize to reclaim unused space and defrag the data.

So should I being running OPTIMIZE twice? Once when I delete the data, and then again after I've reinserted it? or just once? and if just once, should it be after loading the new data? or after clearing out the old?

Answer 1

it may depend upon whether you are using MyISAM or InnoDB tables, but i would run the OPTIMIZE after truncating the table. This should ensure space is reclaimed and it will run very quickly.

When you insert your batch of data it should all insert in order and not be fragmented anyway, and since it's a fresh insert there will be no space to reclaim. If it's a small dataset it may not matter too much, but on a large dataset doing the OPTIMIZE after the insert could also be quite slow.