Syntax
------ 
OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
 tbl_name [, tbl_name] ...
 [WAIT n | NOWAIT]
 
Description
----------- 
OPTIMIZE TABLE has two main functions. It can either be used
to defragment tables, or to update the InnoDB fulltext
index.
 
WAIT/NOWAIT
 
Set the lock wait timeout. See WAIT and NOWAIT.
 
Defragmenting
 
OPTIMIZE TABLE works for InnoDB (before MariaDB 10.1.1, only
if the innodb_file_per_table server system variable is set),
Aria, MyISAM and ARCHIVE tables, and should be used if you
have deleted a large part of a table or if you have made
many changes to a 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.
 
This statement requires SELECT and INSERT privileges for the
table.
 
By default, OPTIMIZE TABLE statements are written to the
binary log and will be replicated. The NO_WRITE_TO_BINLOG
keyword (LOCAL is an alias) will ensure the statement is not
written to the binary log. 
 
OPTIMIZE TABLE is also supported for partitioned tables. You
can use 
ALTER TABLE ... OPTIMIZE PARTITION 
to optimize one or more partitions.
 
You can use OPTIMIZE TABLE to reclaim the unused
space and to defragment the data file. With other storage
engines, OPTIMIZE TABLE does nothing by default, and returns
this message: " The storage engine for the table doesn't
support optimize". However, if the server has been started
with the --skip-new option, OPTIMIZE TABLE is linked to
ALTER TABLE, and recreates the table. This operation frees
the unused space and updates index statistics.
 
Since MariaDB 5.3, the Aria storage engine supports progress
reporting for this statement.
 
If a MyISAM table is fragmented, concurrent inserts will not
be performed until an OPTIMIZE TABLE statement is executed
on that table, unless the concurrent_insert server system
variable is set to ALWAYS.
 
Updating an InnoDB fulltext index
 
When rows are added or deleted to an InnoDB fulltext index,
the index is not immediately re-organized, as this can be an
expensive operation. Change statistics are stored in a
separate location . The fulltext index is only fully
re-organized when an OPTIMIZE TABLE statement is run.
 
By default, an OPTIMIZE TABLE will defragment a table. In
order to use it to update fulltext index statistics, the
innodb_optimize_fulltext_only system variable must be set to
1. This is intended to be a temporary setting, and should be
reset to 0 once the fulltext index has been re-organized.
 
Since fulltext re-organization can take a long time, the
innodb_ft_num_word_optimize variable limits the
re-organization to a number of words (2000 by default). You
can run multiple OPTIMIZE statements to fully re-organize
the index.
 
Defragmenting InnoDB tablespaces
 
MariaDB 10.1.1 merged the Facebook/Kakao defragmentation
patch 
 
MariaDB 10.1.1 merged the Facebook/Kakao defragmentation
patch, allowing one to use OPTIMIZE TABLE to defragment
InnoDB tablespaces. For this functionality to be enabled,
the innodb_defragment system variable must be enabled. No
new tables are created and there is no need to copy data
from old tables to new tables. Instead, this feature loads n
pages (determined by innodb-defragment-n-pages) and tries to
move records so that pages would be full of records and then
frees pages that are fully empty after the operation. Note
that tablespace files (including ibdata1) will not shrink as
the result of defragmentation, but one will get better
memory utilization in the InnoDB buffer pool as there are
fewer data pages in use.
 
See Defragmenting InnoDB Tablespaces for more details.
 


URL: https://mariadb.com/kb/en/optimize-table/