Flite Careers

Do as I Do, Not as I Say

One of the ways I try to contribute to the MySQL community is by answering questions on Stack Overflow. Based on the votes my answers get, I can tell that some of them are helpful only to the person who asked the question (if that), but a few of my answers continue to get upvotes for months and even years.

For example, here’s my most popular answer. If you don’t want to follow the link, the question is essentially “How do I optimize all tables in a MySQL instance with a single command?” and my answer is “mysqlcheck -o”.

Apparently that is a common problem, and lots of people find my solution useful, which is nice. But would I ever actually run mysqlcheck -o? Probably not. At least, not on a production database.

I wouldn’t run it on an active database because it locks the tables. I wouldn’t run it on a passive master database because mysqlcheck does not have a local option, so the optimize table statements would be written to the binary log and replicated to the slaves.

I suppose I might use mysqlcheck -o to optimize all of the tables on a passive slave database, but I tend to want a little more control when I optimize tables so I wrote my own script for doing it. Before running, the script confirms that it’s running on a passive database and that it has enough free disk space to rebuild the largest table in the database. I also pause in between optimizing each table to let replication catch up, and there are certain tables that I do not optimize at all for various reasons.

Of course all of this talk about how to optimize tables avoids the question of if and when you should optimize tables. I’ll avoid a lengthy discussion of that subject and recommend Baron’s post from a couple of years ago. But I will say anecdotally that some people conflate query optimization with optimize table since they both use the word “optimize”. In my opinion query optimization is always a good thing, and optimize table is often helpful, but not always.