13.5.2.5. OPTIMIZE TABLE Syntax
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...
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
VARCHAR, VARBINARY,
BLOB, or TEXT columns).
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 OPTIMIZE
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
tables.
OPTIMIZE TABLE works only for
MyISAM, BDB, and
InnoDB tables.
For MyISAM tables, OPTIMIZE
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),
update them.
For BDB tables, OPTIMIZE
TABLE currently is mapped to ANALYZE
TABLE. See Section 13.5.2.1, “ANALYZE TABLE Syntax”.
For InnoDB tables, OPTIMIZE
TABLE is mapped to ALTER TABLE,
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
--skip-new or --safe-mode
option. In this case, OPTIMIZE TABLE is
just mapped to ALTER TABLE.
OPTIMIZE TABLE returns a result set with
the following columns:
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.
