OPTIMIZE TABLE
Syntax
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE tbl_name [, tbl_name] ...
Description
OPTIMIZE TABLE
has two main functions. It can either be used to defragment tables, or to update the InnoDB fulltext index.
Defragmenting
OPTIMIZE TABLE
works for InnoDB (if the innodb_file_per_table
server system variable is set), Aria, MyISAM and ARCHIVE tables, and 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.
This statement requires SELECT and INSERT privileges for the table.
By default, OPTIMIZE TABLE
statements are written to the binary log and will be replicated. The NO_WRITE_TO_BINLOG
keyword (LOCAL
is an alias) will ensure the statement is not written to the binary log.
OPTIMIZE TABLE
is also supported for partitioned tables. You
can use
ALTER TABLE ... OPTIMIZE PARTITION
to optimize one or more partitions.
You can use OPTIMIZE TABLE
to reclaim the unused
space and to defragment the data file. With other storage engines, OPTIMIZE TABLE
does nothing by default, and returns this message: " The storage engine for the table doesn't support optimize". However, if the server has been started with the --skip-new
option, OPTIMIZE TABLE
is linked to ALTER TABLE
, and recreates the table. This operation frees the unused space and updates index statistics.
Since MariaDB 5.3, the Aria storage engine supports progress reporting for this statement.
If a MyISAM table is fragmented, concurrent inserts will not be performed until an OPTIMIZE TABLE
statement is executed on that table, unless the concurrent_insert server system variable is set to ALWAYS
.
Updating an InnoDB fulltext index
When rows are added or deleted to an InnoDB fulltext index, the index is not immediately re-organized, as this can be an expensive operation. Change statistics are stored in a separate location . The fulltext index is only fully re-organized when an OPTIMIZE TABLE
statement is run.
By default, an OPTIMIZE TABLE will defragment a table. In order to use it to update fulltext index statistics, the innodb_optimize_fulltext_only system variable must be set to 1
. This is intended to be a temporary setting, and should be reset to 0
once the fulltext index has been re-organized.
Since fulltext re-organization can take a long time, the innodb_ft_num_word_optimize variable limits the re-organization to a number of words (2000 by default). You can run multiple OPTIMIZE statements to fully re-organize the index.