top of page
CerebroSQL

MySQL: 

OPTIMIZE TABLE

Syntax:
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name [, tbl_name] ...

OPTIMIZE TABLE reorganizes the physical storage of table data and
associated index data, to reduce storage space and improve I/O
efficiency when accessing the table. The exact changes made to each
table depend on the storage engine used by that table.

Use OPTIMIZE TABLE in these cases, depending on the type of table:

o After doing substantial insert, update, or delete operations on an
InnoDB table that has its own .ibd file because it was created with
the innodb_file_per_table option enabled. The table and indexes are
reorganized, and disk space can be reclaimed for use by the operating
system.

o After doing substantial insert, update, or delete operations on
columns that are part of a FULLTEXT index in an InnoDB table. Set the
configuration option innodb_optimize_fulltext_only=1 first. To keep
the index maintenance period to a reasonable time, set the
innodb_ft_num_word_optimize option to specify how many words to
update in the search index, and run a sequence of OPTIMIZE TABLE
statements until the search index is fully updated.

o After deleting a large part of a MyISAM or ARCHIVE table, or making
many changes to a MyISAM or ARCHIVE 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. After extensive changes
to a table, this statement may also improve performance of statements
that use the table, sometimes significantly.

This statement requires SELECT and INSERT privileges for the table.

OPTIMIZE TABLE works for InnoDB, MyISAM, and ARCHIVE tables. OPTIMIZE
TABLE is also supported for dynamic columns of in-memory NDB tables. It
does not work for fixed-width columns of in-memory tables, nor does it
work for Disk Data tables. The performance of OPTIMIZE on NDB Cluster
tables can be tuned using --ndb-optimization-delay, which controls the
length of time to wait between processing batches of rows by OPTIMIZE
TABLE. For more information, see
https://dev.mysql.com/doc/refman/8.0/en/mysql-cluster-limitations-resol
ved.html.

For NDB Cluster tables, OPTIMIZE TABLE can be interrupted by (for
example) killing the SQL thread performing the OPTIMIZE operation.

By default, OPTIMIZE TABLE does not work for tables created using any
other storage engine and returns a result indicating this lack of
support. You can make OPTIMIZE TABLE work for other storage engines by
starting mysqld with the --skip-new option. In this case, OPTIMIZE
TABLE is just mapped to ALTER TABLE.

This statement does not work with views.

OPTIMIZE TABLE is supported for partitioned tables. For information
about using this statement with partitioned tables and table
partitions, see
https://dev.mysql.com/doc/refman/8.0/en/partitioning-maintenance.html.

By default, the server writes OPTIMIZE TABLE statements to the binary
log so that they replicate to replicas. To suppress logging, specify
the optional NO_WRITE_TO_BINLOG keyword or its alias LOCAL.

URL: https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

Example

bottom of page