Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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 compliance, the optimize table simply copy all records into 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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