I have a table that's always used 24/7. There is always a stream of update and/or insert of 10-20 rows per 30 to 60 seconds.

As stated on the MYSQL documentation: "After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system."

I am not sure if what im doing with the table is "substantial" enough for me to optimize it regulary.

Also, what is the impact on performance with doing optimize table regulary?

1 Answer 1


In almost all situations, OPTIMIZE TABLE is a waste of time and effort. So, in my opinion, "substantial" is really, really, big.

10-20 changes per minute -- not very busy. Not substantial.

  • does a hundred thousand data count as substantial? or maybe a million? Sep 14, 2015 at 4:17
  • 1
    Per minute, yes. Per table, no. Please provide SHOW CREATE TABLE and SHOW TABLE STATUS; these may give clues to discuss.
    – Rick James
    Sep 14, 2015 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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