Search code examples
mysqloptimizationprocessinnodbkill

Is it safe to KILL an OPTIMIZE on InnoDB?

Is it safe to kill an OPTIMIZE query on an InnoDB table without corrupting the table?

The MySQL docs says:

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

But it doesn't say anything about InnoDB.

Solution

  • If you OPTIMIZE InnoDB table it creates a new index structure for it and copies records from the old table to the new. When the copy is done it then switches the tables.

    If you kill OPTIMIZE in the middle of this InnoDB will have to rollback.

    It is safe, but it may take long time to rollback.