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. This statement does not work with views.

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 is also supported for partitioned tables. For
information about using this statement with partitioned tables and
table partitions, see
http://dev.mysql.com/doc/refman/5.7/en/partitioning-maintenance.html.

In MySQL 5.7.1, gtid_next must be set to AUTOMATIC before issuing this
statement. This restriction does not apply in MySQL 5.7.2 or later.
(Bug #16062608, Bug #16715809, Bug #69045)

URL: http://dev.mysql.com/doc/refman/5.7/en/optimize-table.html

