WP-Mix

This tutorial explains how to use WordPress’ built-in tools for optimizing database tables that are of the InnoDB type. Also works for good ’ol MyISAM database tables.

MyISAM vs InnoDB

If your WordPress installation is using MyISAM database tables, it is trivial to optimize and repair them via phpMyAdmin or similar tools. But the trend these days is moving away from MyISAM and toward InnoDB. Unfortunately, InnoDB tables may not be optimized or repaired via phpMyAdmin, which returns the following error:

“The storage engine for the table doesn’t support repair.”

WordPress to the rescue

Fortunately, WordPress provides a built-in tool that enables repair of either MyISAM or InnoDB, so it doesn’t matter which one you are using. In order to use the tool, it must be enabled via your site’s wp-config.php. You can do so by adding the following definition:

define('WP_ALLOW_REPAIR', true);

Add that line in your wp-config.php file, just above the line that says, “That’s all, stop editing! Happy blogging.” Then once that is uploaded to the server, you can visit the WP Database Repair Tool via the following URL:

http://example.com/wp-admin/maint/repair.php

There you will find two buttons, one for repairing and one for repairing and optimizing. Here is a screenshot so you can see what it looks like. Pretty basic, but works well.

Upon clicking the repair button, WordPress will attempt to repair all tables in your database. If there are tables that cannot be repaired, WordPress will let you know. Similarly with repairing and optimizing: upon clicking the button, WordPress will attempt to repair and optimize all tables. Any failures will be reported on the results screen.

Tip: if the repair or optimization does not work the first time, try running the process again. Sometimes multiple attempts are required to do the job.

Caveats and notes

Before running off and trying this technique, keep in mind the following important points:

• The WP DB Optimization Tool works for either MyISAM or InnoDB.
• After you enable the tool via wp-config.php, anyone can access and optimize/repair your database by visiting the URL mentioned above.
• Thus, it is important to remove the WP_ALLOW_REPAIR definition from wp-config.php to disable the tool when you are through using it.
• Alternately, you can disable the tool by changing true to false in the WP_ALLOW_REPAIR definition.

For more information about the WP_ALLOW_REPAIR definition, visit the WP Codex.

Update: now available as a plugin!

Don’t want to mess with editing your configuration file? Grab a copy of Enable WP Database Tools over at Perishable Press :)