W-Shadow.com

Now that spring has arrived (at least in the Northern Hemisphere), it is an excellent time to clean up and decruft your WordPress site. In this post I will show you a number of plugins and techniques that you can use to do just that.

Note: Back up your database before running any of the plugins or queries presented below.

Delete inactive plugins and themes

Not only do inactive plugins and themes waste disk space, they also clutter your database with unused settings and other metadata. Remember: most plugins won’t delete their DB options and tables until you completely uninstall them. You will also continue to receive update notifications, which can be a distraction. Remove any plugins and themes you’re not using any more.

Delete orphaned DB options

As mentioned above, plugins should automatically clean up their database entries when you uninstall them. Unfortunately, not all of them do, which leads to some entries becoming “orphaned”. Use the Clean Options plugin to delete DB options left behind by those untidy plugins.

Delete orphaned database tables

First, use WP-DBManager to get a list of all tables in your database. Compare it to the list of WordPress core tables. Any table not on that list is part of a plugin. If you spot any tables that have been orphaned by uninstalled plugins, use the “drop” command to delete them.

Delete post revisions

The post revision feature can be useful, but it also tends to fill up your database pretty quickly since WordPress creates a new revision each time you edit a post. There are several ways to fix this problem.

Delete all post revisions

You can use WP-Cleanfix or WP-Optimize to get rid of any and all post revisions.

Delete only old post revisions

In some cases you may want to clean out old revisions while leaving the most recent – and thus most likely to be useful – revisions intact. This SQL query will delete only post revisions created before March 1st, 2012:

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' AND
	a.post_date < '2012-03-1'

Use WP-DBManager or a similar plugin to run this query.

Limit the number of revisions per post

Add this line to your wp-config.php file to limit the number of revisions to 5 per post:

define('WP_POST_REVISIONS', 5);

Disable revisions

Add this line to your wp-config.php to completely disable post revisions:

define('WP_POST_REVISIONS', 0);

Note that this does not remove existing revisions. Use one of the other techniques discussed above to do that.

Delete spam comments

Even small blogs can receive thousands of spam comments per month. If left unchecked, all the spam will gradually accumulate in your database, potentially slowing down comment queries and hurting your site’s performance. Make sure to regularly clean out the spam by clicking the “Empty Spam” button in Comments -> Spam.

Bonus tip: I strongy recommend the NoSpamNX plugin to anyone receiving large amounts of spam. It’s a great “first line of defence” and can be configured to discard spam comments immediately instead of storing them in the database.

Delete pingbacks

If your comments table still takes up a lot of space even after you’ve deleted all spam comments, consider removing some or all the received pingbacks. While pingbacks can be useful as a way to be notified when someone mentions your content, old pingbacks lose their relevance quickly. Pingbacks are also frequently abused by spammers.

This query will delete all pingbacks received before March 1st, 2012:

DELETE FROM wp_comments
WHERE
   comment_type = 'pingback' AND
   comment_date < '2012-03-01';

Scan your site for malware

Check that your blog is free from malware and suspicious code by scanning it with Sucuri.net.

Find and fix broken links

Use Broken Link Checker to check your site for broken links, missing images, removed YouTube videos and similar issues.

Delete unused post and comment meta

Unused post meta keys are another kind of database cruft that can be left behind by long-since-uninstalled plugins and themes. You can use the WP CleanFix plugin I mentioned before or WP-Cleanup to remove them.

Alternatively, if you would prefer to do it without a plugin, the following SQL query will also remove all instances of the specified post meta key:

DELETE FROM wp_postmeta WHERE meta_key = 'unused meta-key-name';

And this query will do the same for the comment meta table:

DELETE FROM wp_commentmeta WHERE meta_key = 'unused-meta-key-name';

Finally, if you have a lot of registered users, you might also want to check for and remove unused user meta keys. Here’s a query to delete an unused piece of user metadata:

DELETE FROM wp_usermeta WHERE meta_key = 'unused-meta-key-name';

Also, see this tutorial for a more in-depth explanation of how to find and remove unused custom fields with phpMyAdmin.

Remove unused tags and categories

If you have a lot of unused tags or categories on your site, clean them up to reduce the database size and gain a little bit of performance. Both WP CleanFix and WP-Cleanup can help you get rid of unused tags.

Alternatively, this set of SQL queries will accomplish the same task (props to 4 Rapid Development):

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE COUNT = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id NOT IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id NOT IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Remove unused shortcodes

If you’ve been blogging for a while, you’ve probably run into this problem: you uninstall a plugin or switch to a different theme, and now your site is full of shortcodes that no longer work. Obviously, going back and editing each post one-by-one is not going to work. You need a way to automatically remove the unused shortcodes.

Well, Search and Replace is just the ticket. It lets you search your posts for a piece of text and replace it something else.

Alternatively, here’s a query that will remove all instances of the specified [shortcode] from your posts (props to WPRecipes):

UPDATE wp_post SET post_content = replace(post_content, '[shortcode]', '' ) ;

Note that the above only works with self-closing shortcodes. For self-closing shortcodes like “[shortcode] content [/shortcode]” you’ll need to use regular expression search.

Delete commenter’s user agent

For each comment your blog receives, WordPress also stores the comment author’s user agent (basically, the web browser fingerprint) along with the rest of the comment. This can be handy for advanced users, but most bloggers will never need this information. You can shrink your database a bit by removing the user agent records.

Run this query to remove the user agent information from your database (props to RSA Blog):

UPDATE wp_comments SET comment_agent = '';

Delete unused images

Free up some disk space by deleting images not attached to any post or page. Go to Media -> Library -> Unattached, select all items, choose “Delete Permanently” from the “Bulk Action” drop-down and click “Apply”.

Another option is to use Upload Janitor or WP CleanFix to automate the process.

Optimize database tables

It’s good practice to optimize your tables regularly. Database optimization helps keep site performance steady and is especially useful after you’ve just made large change to the DB (like cleaning up all post revisions and unused meta keys). You can use WP-DBManager to schedule automatic DB optimization. WP-Optimize also includes the database optimization feature, but does not support scheduling.

Resources

For reference, here’s a list of all plugins mentioned in this post (in alphabetical order):

• Broken Link Checker – scan your site for broken links.
• Clean Options – detect and remove orphaned DB options.
• NoSpamNX – block spam comments.
• Search and Replace – find & replace text in your posts, comments, etc.
• Search Regex – find & replace with regular expression support.
• Upload Janitor – find and delete unused images.
• WP CleanFix – delete post revisions, unused meta, and much more.
• WP-Cleanup – delete revisions, unused tags, post meta, etc.
• WP-DBManager – manage and optimize your database.
• WP-Optimize – remove revisions, auto-draft posts, optimize tables, etc.

Final note: As you may have noticed, many of the “clean up” plugins – WP-Cleanup, WP-CleanFix, and so on – have a very similar feature set. So which should you use? Overall, WP CleanFix seems to be the most comprehensive solution, so I would recommend that one.

a href=”http://wordpress.org/extend/plugins/broken-link-checker/”