These are some common steps I take when I need to cleanup a WordPress database, either during a migration, during a build, or sometimes just to improve performance. All of these are SQL commands and would need to be executed on your database, either through the command line, a web interface like phpMyAdmin, or a GUI like Sequel Pro.
Note: always backup your database before running raw commands like this…ya know…just in case.
Another note: if you aren’t using the default ‘wp_’ table prefix, you’ll need to update the table names in the commands below.
Clear Revisions
If you have the default revisions settings going on a WordPress site, it’ll save ALL revisions in the database under a special post type, along with all their meta data and taxonomy terms. This can very quickly add up to massive amounts of needless content in the database. Run this command against the database to clear that junk out:
DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision'
You can also limit the number of revisions or turn them off entirely if you do not think you’ll need them on your site by modifying the wp_config.php file and setting the ‘WP_POST_REVISIONS’ constant.
Clear All Transients
The WordPress Transients API stores cached data in the database. Sometimes you’ll need to clear those when working on applications and need to see updated information. This is how:
DELETE FROM `wp_options` WHERE `option_name` LIKE ('_transient_%');
DELETE FROM `wp_options` WHERE `option_name` LIKE ('_site_transient_%');
Delete All Comments
A lot of WordPress sites have no business having commenting enabled but the settings and theme aren’t set to provide bots and spam from hitting the site anyway. First you should close up your discussion settings. And then use these commands to easily clear out all that garbage.
TRUNCATE `wp_commentmeta`; TRUNCATE `wp_comments`;