From time to time it is a good idea to do a little housekeeping with your WordPress installation, especially if you’ve been experimenting with and deleting plugins. MySQL databases don’t clean themselves. Optimizing your database is something akin to when you defrag your hard drive or change the oil in your car. It will help to free up disk space and keep your database running smoothly.

How to Optimize Your WordPress Database

Optimizing database tables can be done manually using phpMyAdmin or by using a plugin. I’m of the opinion that if you don’t absolutely require a plugin to do it, then it’s best not to add another. However, if you’re not familiar with phpMyAdmin, then a plugin may be a safer option for you.

Plugin Options:

WP-Optimize: This is a database cleanup and optimization tool. It allows you to remove post revisions, comments in the spam queue, un-approved comments within few clicks.

WP Database Optimizer: This plugin allows you to optimize your WordPress tables on a schedule. It uses the WordPress Database Class to do the work, and you can set the number of days in between optimizations.

Clean Options: This plugin finds orphaned options and allows for their removal from the wp_options table.

Optimization using phpMyAdmin:

If you’re slightly more adventurous and you want to get a hands-on idea of what’s going on with your database, open up phpMyAdmin.

Step 1: Check for Overhead

Step 2: Choose “Check All Tables Having Overhead”

As shown in the image above, you’ll be able to check all the tables that need to be optimized. Once they are checked, select “optimize table” from the drop down menu. This will take care of all of your tables at once. You should see this kind of success message:

How to Repair WordPress Tables

Occasionally you may have a table crash. When this happens, one symptom might be that all of your posts seem to have disappeared, or perhaps all of your drafts. You may even get a message in phpMyAdmin telling you that one of your tables haves crashed. No need to worry. Repairing tables is very easy.

Step 1: Login to phpMyAdmin and select the affected database

Choose the database that pertains to your WordPress blog. If you have only one it should be automatically selected.

Step 2: Check the boxes by the tables that need repair.

Once the boxes are selected, go to the drop down menu and choose “Repair Table”. You should see a success message when it’s completed.

How to Automatically Repair Your WordPress Database

Occasionally, your database may become so corrupt that you cannot login. This is a time when you might want to enable WordPress’ own ability to optimize the database. WordPress 2.9 and later has automatic database optimization support, which you can enable by adding the following define to your wp-config.php file only when the feature is required:

define('WP_ALLOW_REPAIR', TRUE);

You can find the script it uses at: at {$your_site}/wp-admin/maint/repair.php

Above all else, make sure to backup your database before making major optimization efforts or attempting to repair tables. You may thank yourself later. There are many more ways that you can optimize your WordPress database, but these are a couple of the basics to get you started. Set up a regular schedule for optimization and keep your sites running smoothly!