CODEWITHSUNDEEP
mysqloptimization
pedalpete
pedalpete

Reputation: 21536

when to use OPTIMIZE in mysql

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?

Upvotes: 7

Views: 2455

Answers (3)

CHINTU RANA
CHINTU RANA

Reputation: 97

After deleteing or updateing a set of data into your database,you can use optimize table command to remove de-fragmented space .

there is no need to use optimize command two time.after all DML process you can use optimize command.

Upvotes: 0

Jarod Elliott
Jarod Elliott

Reputation: 15670

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.

Upvotes: 6

womble
womble

Reputation: 12407

Just once is fine, after you've imported the new data.

Upvotes: 0

Related Questions