OPTIMIZE TABLE is a maintenance operation used to recover the disk space and improve IO efficiency. It is recommended that the operation is carried out under the cases noted in the optimize table documentation.

MySQL versions prior to MySQL 5.6.17 does not allow concurrent changes (inserts, updates, deletes) to the table when the OPTIMIZE TABLE operation is being performed on that table. This causes downtime for user applications and is relatively high for large tables.

OPTIMIZE TABLE for INNODB or PARTITIONED INNODB tables used ALTER TABLE FORCE operation internally to address the fragmentation problem prior to MySQL 5.6.17. This involves doing a table copy and taking a strong lock in the SQL-layer which blocks all concurrent changes to the table, thus causing the downtime.

MySQL supports online rebuild of tables within the storage engine for INNODB starting from MySQL 5.6.4. This capability has been used utilized to improve the following operations for versions starting from MySQL 5.6.17:

a) OPTIMIZE TABLE.
b) ALTER TABLE FORCE and
c) ALTER TABLE ENGINE= INNODB(Same engine during creation)

The online rebuild involves taking strong lock only for brief interval of time thus reducing the downtime for the operations. Hence while performing the OPTIMIZE TABLE maintenance operation, concurrent changes(inserts, updates and deletes) to the table can be performed in parallel.

Also operations ‘b’ and ‘c’ involves rebuild of the table and the support of online rebuild is enabled for versions starting from MySQL 5.6.17.

Note that these operations will continue to use table copy under any of the below conditions:

a) ‘old_alter_table’ system variable is turned ON.

b) ‘skip-new’ mysqld option is enabled and OPTIMIZE TABLE operation is performed.

c) Algorithm ‘COPY’ is explicitly specified for the ALTER TABLE operations.

d) The table contains FULLTEXT indexes.