13.7.2.4 OPTIMIZE TABLE Syntax

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

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.

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.

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.

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.

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.

InnoDB Details

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:

Prior to Mysql 5.7.4, OPTIMIZE
TABLE does not use
online DDL
(ALGORITHM=INPLACE). Consequently, concurrent
DML (INSERT,
UPDATE,
DELETE) is not permitted on a
table while OPTIMIZE TABLE is
running, i.e. the table is locked. Also, secondary indexes are
not created as efficiently because keys are inserted in the
order they appeared in the primary key.

As of 5.7.4, OPTIMIZE TABLE uses
online DDL
(ALGORITHM=INPLACE) for both regular and
partitioned InnoDB tables. The table rebuild,
triggered by OPTIMIZE TABLE and
performed under the cover by
ALTER TABLE ...
FORCE, is now performed using
online DDL
(ALGORITHM=INPLACE) and only locks the table
for a brief interval, which reduces downtime for concurrent DML
operations.

OPTIMIZE TABLE continues to use
ALGORITHM=COPY under the following
conditions:

OPTIMIZE TABLE using
online DDL
(ALGORITHM=INPLACE) is not supported for
InnoDB tables that contain
FULLTEXT indexes.
ALGORITHM=COPY must be 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)
will. When considering whether or not to run optimize, consider
the workload of transactions that your server will 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.

Other Considerations

For InnoDB tables prior to 5.7.4 and other
table types, MySQL locks the
table during the time OPTIMIZE
TABLE is running. As of MySQL 5.7.4,
OPTIMIZE TABLE is performed
online for regular and partitioned InnoDB
tables.

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

OPTIMIZE TABLE does not sort
R-tree indexes, such as spatial indexes on
POINT columns. (Bug #23578)

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 .frm,
.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, if you have your tables in one tablespace, this will make a complete copy of the table within the tablespace, making the tablespace larger by the total table size less the free space you started with. It will not reduce the tablespace size. It can free fragmented space within a table to the tablespace, making that space available to other tables. If you're short of disk space and don't want to enlarge the tablespace you may be able to work around this by altering the table to MyISAM and then back to InnoDB.

Also at MyISAM tables, the optimize needs a whole datafile of free hd space to free the not-used space in the file.This means, that you need at least (database + data of biggest table) storage at the database directory (my case).

This may be very unfortunate, if you have (some, but) one very big table in your database, which needs almost all the storage... ;(

In this case, it is a very bad idea to optimize this table. Indeed, you can not optimize it, until the data of that big table is less then free space, which is very unlikely (This table must shrink to about eg. 1% its data).

Just tested: mysql will wait for space on the storage, if not enough is available for TABLE OPTIMIZE. Of course, this table will be locked all the time. I got out of this situation only by stopping the mysql server.

The only help I know, is to copy this table to another server with enough space, then optimized it there, and move this optimized table back (which must be done offline, because you have to remove the original first to get the space)

"Optimize table" locks the table, which made it impossible to use on the systems I worked with. We just couldn't wait for MySQL to clean up tens of tables which were huge (gigabytes) while the system was effectively down.

Thus I wrote my own "optimize table" perl-script: it builds the table from scratch while it still can be used by other scripts through a merge-table solution.

Of course there are a few cave-ats: see the explanation in the script. But for us this script turned out to be very very useful. We could clean tables while we could still use them as if nothing was happening.

Basically it sets up a new MyISAM merge-table and a copy of the old table, and then starts to fill a newly created clean table with all data from the old un-optimized table. At some point all data has been moved and the switch back is made: removal of the merge-table and renaming of the new and optimized table to the appropriate table-name.