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.