Re: when is the proper time to execute optimize table command ?
Posted by: Rick James
Date: August 22, 2015 03:23PM

Simple answer: Never.

Longer answer: In analyzing well over a thousand "slow" queries, I have only twice come to the conclusion that a regular OPTIMIZE TABLE was warranted. Both of those were MyISAM tables, not InnoDB tables, and both said "once a month" was sufficient. A _few_ InnoDB cases came close to needing OPTIMIZE.

There are two reasons for OPTIMIZE:
* Defragment for speed. This is rarely useful.
* Free up disk space. This tends to be a one-time desperation effort, it it is fraught with many caveats and cautions.

Really long answer: I won't go into the details about
* Engine differences
* Disk layout
* Disk access patterns
* BTree structure (and waste space)
* Data versus Index defragmentation
* PARTITION problems
* How long OPTIMIZE takes
* SSD vs spinning drives
* RAID with write cache
* Etc.

Options: ReplyQuote

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.