OPTIMIZE TABLE Syntax
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE
OPTIMIZE TABLE should be used if you have
deleted a large part of a table or if you have made many
changes to a table with variable-length rows (tables that have
Deleted rows are maintained in a linked list and subsequent
INSERT operations reuse old row positions.
You can use
OPTIMIZE TABLE to reclaim the
unused space and to defragment the data file.
In most setups, you need not run
TABLE at all. Even if you do a lot of updates to
variable-length rows, it is not likely that you need to do
this more than once a week or month and only on certain
OPTIMIZE TABLE works only for
TABLE works as follows:
If the table has deleted or split rows, repair the table.
If the index pages are not sorted, sort them.
If the table's statistics are not up to date (and the
repair could not be accomplished by sorting the index),
TABLE currently is mapped to
TABLE. See Section 220.127.116.11, “
ANALYZE TABLE Syntax”.
TABLE is mapped to
which rebuilds the table to update index statistics and free
unused space in the clustered index.
You can make
OPTIMIZE TABLE work on other
storage engines by starting mysqld with the
option. In this case,
OPTIMIZE TABLE is
just mapped to
OPTIMIZE TABLE returns a result set with
the following columns:
|The table name
Note that MySQL locks the table during the time
OPTIMIZE TABLE is running.
OPTIMIZE TABLE statements are written to
the binary log unless the optional
NO_WRITE_TO_BINLOG keyword(or its alias
LOCAL) is used. This is done so that
OPTIMIZE TABLE statements used on a MySQL
server acting as a replication master will be replicated by
default to the replication slave.