Can I safely kill OPTIMIZE TABLE on an InnoDB table?

MySQL's documentation for kill warns:


Warning


Killing a REPAIR TABLE or OPTIMIZE TABLE operation on a MyISAM table results in a table that is corrupted and unusable. Any reads or writes to such a table fail until you optimize or repair it again (without interruption).

That's for MyISAM.

Is it also unsafe to kill an OPTIMIZE TABLE process running against an InnoDB table?

According to MySQL Certification Guide :


The OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting
it. This involves reclaiming unused space resulting from deletes and
updates, and coalescing records that have become split and stored
non-contiguously. OPTIMIZE TABLE also sorts the index pages if they
are out of order and updates the index statistics


Also works for InnoDB tables, but maps to ALTER TABLE, which rebuilds
the table. This updates index statistics and frees space in the
clustered index.

So In Case of InnoDB :

Innodb is ACID compliant, the optimize table simply copies all records into a new table

• If the index pages are not sorted, sort them,

• If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.

• If the table has deleted or split rows, repair the table.

And if you try to kill it when it's running you will not loose any records.

Additionally, case of InnoDB with innodb_defragment=1, there will be no ROLLBACK required, as in this case OPTIMIZE TABLE is incremental.

StackExchange Database Administrators Q#18479, answer score: 12