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.
Subject
Written By
Posted
Re: when is the proper time to execute optimize table command ?
August 22, 2015 03:23PM
September 22, 2015 01:49AM
September 17, 2015 06:33AM
September 18, 2015 11:43PM
September 18, 2015 11:55PM
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.