Periodically Optimize Your WordPress Database Using Cron Jobs

Posted on Updated on

We’ve already stressed the importance of having a lean and optimized database for your WordPress website. Over a period of time, MySQL tables get bloated and benefit greatly from some repairs and maintenance work. As we’ve seen before, you can do this manually using the phpMyAdmin web application or even the SSH command line. Naturally you won’t remember to do this by yourself at periodic intervals, so you need some kind of tool that performs this automatically. As expected, there are a number of WordPress plug-ins that get the job done, and we had looked at one of these in an earlier post.

But what if you’re paranoid about WordPress bloat and don’t want to add yet another plug-in to your site? Luckily for us, most web control panels have the functionality to execute what are known as “Cron jobs”. In fact, WordPress itself has its own Cron like schedule and system, but many consider it inefficient and also a bit unreliable since it only triggers when someone actually visits your site. For these reasons, if you can get away with creating a Cron job directly on the server instead of through the WordPress system, that will provide you with superior performance, and is also quite a bit simpler.

So let’s look at how to optimize the databases automatically from the web control panel so that our WordPress site itself is untouched and doesn’t experience any additional load.

Setting up the cPanel Cron Job

Since this is WordPress we’re talking about, I’m going to assume that you have a Linux server with a cPanel interface. If so, log into your cPanel account and scroll down all the way to the bottom till you find the “Cron Jobs” icon under the “Advanced” section:

select cron jobs

In the resulting screen, the first thing we’re going to do is to set up an email address to which the output of our Cron jobs can be sent. This serves two purposes – it allows us to verify that our task was indeed carried out automatically, and it also lets us know of any problems or errors that may have arisen. For this reason, I always use “verbose” options when creating my Cron jobs.

set email

So go ahead and set the email ID to which you want to the results to be sent. In the next section, we need to decide on the periodicity of the task. How often do we want it to run? In this case since it’s about optimizing our WordPress databases, once per week should be good enough. From the drop-down box, select the “weekly” option as shown below:

once per week run

This will automatically fill in the appropriate values for a weekly Cron job. If you know what you’re doing, you can modify these parameters to fine-tune exactly when you want it to run – namely which day of the week, and at what time.

In the text field for “command”, type in the following:

mysqlcheck --all-databases --auto-repair --optimize --verbose

This will perform the optimization function for all tables in all databases – a good thing to do regularly. If on the other hand, you wish to only optimize the specific WordPress database in question, use the following command instead:

mysqlcheck [database name] -u [username]-p[password] --auto-repair --optimize --verbose

Note that the “password” you provide has to immediately follow the “-p” option without any blank spaces. Your username and password credentials will be specific to the database in question. After you’ve entered the command, save the task, and you’re done.

If you want to test out whether the Cron job works, you can create a new one with a delay of a minute and you will get the report of whether or not the task ran properly in your email. For example, here’s the one I received after my initial optimization schedule  had executed:

output from database

Once you’re sure it’s working fine, delete the “per-minute” Cron job leaving only the weekly one. Now you need never worry again about your WordPress databases being inefficient!

Leave a Reply

Your email address will not be published. Required fields are marked *