Do you automatically optimize MySQL tables?

As time passes, a MySQL database table can get “fragmented” by adding and deleting records. In theory this can slow down a database (although in practice we rarely see any issues at all from this because we run MySQL databases from fast solid state drives with very little latency).

To minimize any effects of fragmentation, we automatically run MySQL’s OPTIMIZE TABLE command once a month on database tables that are smaller than 200 MB.

Optimizing a table blocks (delays) INSERTs and UPDATEs for a few seconds, so we do it late on Saturday nights to avoid potentially slowing down your scripts at a busy time of the day.

We don’t automatically optimize tables larger than 200 MB, because doing so might delay scripts for more than a few seconds. If you have tables larger than 200 MB that often change, you may want to occasionally optimize the table yourself — although again, in practice this is very rarely necessary on our servers (and we’ll probably tell you if it is).