Syntax

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...
    [WAIT n | NOWAIT]

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 (before MariaDB 10.1.1, only 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.

Defragmenting InnoDB tablespaces

MariaDB 10.1.1 merged the Facebook/Kakao defragmentation patch, allowing one to use OPTIMIZE TABLE to defragment InnoDB tablespaces. For this functionality to be enabled, the innodb_defragment system variable must be enabled. No new tables are created and there is no need to copy data from old tables to new tables. Instead, this feature loads n pages (determined by innodb-defragment-n-pages) and tries to move records so that pages would be full of records and then frees pages that are fully empty after the operation. Note that tablespace files (including ibdata1) will not shrink as the result of defragmentation, but one will get better memory utilization in the InnoDB buffer pool as there are fewer data pages in use.

See Defragmenting InnoDB Tablespaces for more details.

See Also

Optimize table in InnoDB