WordPress is one of the most customizable Content Management Systems or CMSs in the industry. It’s not an exaggeration to say that you can modify every single aspect of your installation either by delving into the code yourself or via plug-ins. But this intense focus on flexibility comes at a price. When any person can write a plug-in that integrates with your database, there’s no guarantee that they will all cleanup after themselves.
While your fresh WordPress installation will be squeaky clean and fast, over time little hiccups will accumulate along the way. It will generate SQL errors if you care to observe them, and inefficient queries will take their toll on your backend. In many cases, merely uninstalling the plug-in isn’t enough. Well written add-ons make sure that they leave no lasting footprint. But many of them are not well written. Every now and then, doing a bit of housecleaning will go a long way towards keeping your WordPress installation lean and mean.
Generic plug-ins can only go so far when you need to optimize the WordPress database. If you really want to remove all the crud, you need to focus on each and every individual table, ascertain its purpose, and perform maintenance tasks by either deleting it entirely, or removing unnecessary rows. In some cases, this extra overload can result in hundreds of additional megabytes that completely dwarf the actual content on your blog!
If you have a hosted WordPress installation, you will almost certainly have access to the backend and phpMyAdmin. Every experienced WordPress user knows that this is the ultimate web-based tool for managing your WordPress database. Through it, you can run queries, create tables, add new databases, and perform cleanup activities as well as backups. So here’s how you perform basic optimizations in phpMyAdmin.
Cleaning up in phpMyAdmin
You’ll probably find the program in your cPanel user interface. If you don’t know what that is, go to the dashboard given to you by your hosting provider. In the list of items, search for phpMyAdmin and click on it. You need to know your databases username and password. For this, open up the wp-config.php file that’s located in the root directory of your installation. You can view it either via FTP or through the inbuilt file browser given to you by your web host. Inside this file, find the lines that look like:
/** MySQL database username */
/** MySQL database password */
As you can see, these will contain your username and password. Access the phpMyAdmin database using these credentials and select your WordPress database. The name of this database can also be found in wp-config.php along with the username and password.
You’ll be presented with a list of the tables in your installation. To quickly and easily repair and optimize them, scroll down and click the “Check All” link to select everything and in the drop-down box available just next to it, hit “Repair All”. This should quickly perform basic maintenance operations on your database and also give you a summary of how each table was affected. After that, you can repeat the process but this time select “Optimize All” instead. I must warn you to be extremely careful here – one misstep and you can delete your entire database! It’ll be wise to take a backup using the inbuilt export functionality in phpMyAdmin before tinkering.
If you have SSH access to your server, you can do the same using the command line. Just type in:
mysqlcheck -u [username] -p -o [database name]
It will prompt you for your username and password each time. You can also automate the process using cron. In which case you have to provide your credentials like this:
mysqlcheck -u [username] -p [password] -o [database name]
Once you do this, you can observe the status of each table as the program processes it. Here's a sample output:
email@example.com [~]# mysqlcheck -u username -p -o wp194
wp194.wp_commentmeta Table is already up to date
wp194.wp_links Table is already up to date
wp194.wp_lockdowns Table is already up to date
wp194.wp_login_fails Table is already up to date
When you become comfortable with SSH, you'll find that using the command line to accomplish tasks like this is more efficient and hassle free.
What would WordPress be without plug-ins huh? As expected, you can achieve the same effect as above by installing a number of add-ons that will do it for you. Two examples are Optimize Database after Deleting Revisions and TentBlogger Optimize WordPress Database, but there are many others you can use. Experiment with some of them until you find one you like.
Optimizing the database tables is something that you should do regularly. Try and set a schedule for yourself in order to keep things running smoothly.