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:
After doing substantial insert, update, or delete operations on an
InnoDB
table that has its own .ibd file because it was created with theinnodb_file_per_table
option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.After doing substantial insert, update, or delete operations on columns that are part of a
FULLTEXT
index in anInnoDB
table. Set the configuration optioninnodb_optimize_fulltext_only=1
first. To keep the index maintenance period to a reasonable time, set theinnodb_ft_num_word_optimize
option to specify how many words to update in the search index, and run a sequence ofOPTIMIZE TABLE
statements until the search index is fully updated.After deleting a large part of a
MyISAM
orARCHIVE
table, or making many changes to aMyISAM
orARCHIVE
table with variable-length rows (tables that haveVARCHAR
,VARBINARY
,BLOB
, orTEXT
columns). Deleted rows are maintained in a linked list and subsequentINSERT
operations reuse old row positions. You can useOPTIMIZE 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
Section 25.2.7.11, “Previous NDB Cluster Issues Resolved in NDB Cluster 8.4”.
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
Section 26.3.4, “Maintenance of Partitions”.
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
. You must have the
OPTIMIZE_LOCAL_TABLE
privilege to
use this option.
OPTIMIZE TABLE
returns a result
set with the columns shown in the following table.
Column | Value |
---|---|
Table |
The table name |
Op |
Always optimize |
Msg_type |
status , error ,
info , note , or
warning |
Msg_text |
An informational message |
OPTIMIZE TABLE
table catches
and throws any errors that occur while copying table
statistics from the old file to the newly created file. For
example. if the user ID of the owner of the
.MYD
or .MYI
file is
different from the user ID of the mysqld
process, OPTIMIZE TABLE
generates a "cannot change ownership of the file" error unless
mysqld is started by the
root
user.
For InnoDB
tables,
OPTIMIZE TABLE
is mapped to
ALTER TABLE ...
FORCE
, which rebuilds the table to update index
statistics and free unused space in the clustered index. This
is displayed in the output of OPTIMIZE
TABLE
when you run it on an
InnoDB
table, as shown here:
mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status | OK |
+----------+----------+----------+-------------------------------------------------------------------+
OPTIMIZE TABLE
uses
online DDL for
regular and partitioned InnoDB
tables,
which reduces downtime for concurrent DML operations. The
table rebuild triggered by OPTIMIZE
TABLE
is completed in place. An exclusive table lock
is only taken briefly during the prepare phase and the commit
phase of the operation. During the prepare phase, metadata is
updated and an intermediate table is created. During the
commit phase, table metadata changes are committed.
OPTIMIZE TABLE
rebuilds the
table using the table copy method under the following
conditions:
When the
old_alter_table
system variable is enabled.When the server is started with the
--skip-new
option.
OPTIMIZE TABLE
using
online DDL is not
supported for InnoDB
tables that contain
FULLTEXT
indexes. The table copy method is
used instead.
InnoDB
stores data using a page-allocation
method and does not suffer from fragmentation in the same way
that legacy storage engines (such as
MyISAM
) do. When considering whether or not
to run optimize, consider the workload of transactions that
your server is expected to process:
Some level of fragmentation is expected.
InnoDB
only fills pages 93% full, to leave room for updates without having to split pages.Delete operations might leave gaps that leave pages less filled than desired, which could make it worthwhile to optimize the table.
Updates to rows usually rewrite the data within the same page, depending on the data type and row format, when sufficient space is available. See Section 17.9.1.5, “How Compression Works for InnoDB Tables” and Section 17.10, “InnoDB Row Formats”.
High-concurrency workloads might leave gaps in indexes over time, as
InnoDB
retains multiple versions of the same data due through its MVCC mechanism. See Section 17.3, “InnoDB Multi-Versioning”.
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.
OPTIMIZE TABLE
is performed
online for regular and partitioned InnoDB
tables. Otherwise, MySQL locks
the table during the time OPTIMIZE
TABLE
is running.
OPTIMIZE TABLE
does not sort
R-tree indexes, such as spatial indexes on
POINT
columns. (Bug #23578)