8

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?

6

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

  1. If the index pages are not sorted, sort them,
  2. If the table's statistics are not up to date (and the repair could not be accomplished by sorting the index), update them.
  3. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.