OPTIMIZE TABLE

Syntax

Description

OPTIMIZE TABLE has two main functions. It can either be used to defragment tables, or to update the InnoDB fulltext index.

WAIT/NOWAIT

Set the lock wait timeout. See .

Defragmenting

OPTIMIZE TABLE works for (before , only if the server system variable is set), , and 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 , , , or columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions.

This statement requires for the table.

By default, OPTIMIZE TABLE statements are written to the and will be . The NO_WRITE_TO_BINLOG keyword (LOCAL is an alias) will ensure the statement is not written to the binary log.

OPTIMIZE TABLE statements are not logged to the binary log if is set. See also .

OPTIMIZE TABLE is also supported for partitioned tables. You can use[ALTER TABLE](../../../../reference/sql-statements-and-structure/sql-statements/data-definition/alter/alter-table.md) ... 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 , and recreates the table. This operation frees the unused space and updates index statistics.

The storage engine supports for this statement.

If a table is fragmented, will not be performed until an OPTIMIZE TABLE statement is executed on that table, unless the server system variable is set to ALWAYS.

Updating an InnoDB fulltext index

When rows are added or deleted to an InnoDB , 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 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 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

merged the Facebook/Kakao defragmentation patch, allowing one to use OPTIMIZE TABLE to defragment InnoDB tablespaces. For this functionality to be enabled, the 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 ) 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 for more details.

See Also

_{This page is licensed: GPLv2, originally from}