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.