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

