9 ways to clean up WordPress database

September 14, 2011

Since wordpress is database-driven software, as time goes on, your WordPress database tends to get filled with redundant data. You should clean your WordPress database tables to improve the performance of your blog. There are plugins available to clean up your WordPress database easily and quickly from your blog’s admin panel.

  1. Delete post revision
    WordPress version 2.6 and above has a very useful function that creates a revision of that post or page during the publishing of an article. You will be able to see this in the same page as you write the blog post. For eg. see the image below.

    The post revision is a good way to document any changes to your post. But it can also be a waste of database and server resources. You can either turn off post-revision or limit the number of revisions saved per post by adding a line to the wp_config.php file.
    To turn off post-revision
    define(‘WP_POST_REVISIONS’, false);

    To limit the number of revisions to 6
    define(‘WP_POST_REVISIONS’, 6);

    There are many plugins available to delete the existing post revision. If you do not want to disable this feature, just run this once every few months to clean up your database.
    Plugin available to delete post revision: delete-revision, WP Cleanup, WP-Optimize
  2. Remove all spam comments and all unapproved comments
    You may receive tons of spam comments every day. These spam comments and unapproved comments could be taking up a lot of database space.
    Plugin available to remove all spam comments and all unapproved comments: Cleanup-wp, WP Cleanup, WP-Optimize
  3. Remove all unused tags
    Tags are recorded on the wp_terms table. Plugin: WP Cleanupwill search and delete all unused tags in your WordPress database. Alternately if you are a techie guy, run the following query to show all unused tags from wp_terms table. Then you can delete them.SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;
  4. Remove all unused post meta and Comments Meta
    ( i ) When you remove blog posts from your blog, WordPress will leave some post meta in your database which is not used anymore. Plugin: WP Cleanup will search and delete all unused post meta in your WordPress database.
    ( ii ) Clean up wp_commentmeta entries which have no relation to wp_comments Access your database from PhpMyAdmin and run the following SQL.
    SELECT * FROM wp_commentmeta WHERE comment_id
    NOT IN (
    SELECT comment_id
    FROM wp_comments
    DELETE FROM wp_commentmeta WHERE comment_id
    NOT IN (
    SELECT comment_id
    FROM wp_comments

    clean up Akismet related metadata in wp_commentmeta table.
    SELECT * FROM wp_commentmeta WHERE meta_key
    LIKE "%akismet%"
    DELETE FROM wp_commentmeta WHERE meta_key
    LIKE "%akismet%"
  5. Delete unwanted plugin
    Delete the unwanted plugin from your blog dashboard.
  6. Remove unused themes
    You might have tried out different themes on your site and now you picked the one you like the most. Each time you install a theme, you are adding new information to your database and when you uninstall a theme you are not removing the information from your database table . So you need to delete these themes to clean your database.
  7. Remove Orphaned Options
    When you deactivated/uninstall a plugin or theme the options or the tables that the plugin/theme created are not removed. The Clean Options plugin will determine which options are orphaned. You will be able to view them and decide which ones you want to remove from the options table.
  8. Delete unused images
    When you upload an image to WordPress blog, it will automatically resize and create thumbnail and medium size images. Some times you do not use them in the post. These unused images could cause unnecessarily burden to the server and database. The DUI (Delete Unused Images) plugin scans your WordPress blog and find those images that are not used or referenced to by any of the posts/pages. It then proceeds to delete them from the database/server.
  9. Optimize your WordPress database
    It is always good practice to optimize your blog’s database to keep your database running smoothly and thus improve the site performance. You can use wordpress plugins: WP-DBManager, Optimize DB.

WP Cleanup, WP-Optimize WordPress plugin will perform the following actions.

  • Remove all post revisions
  • Remove all spam comments
  • Remove all unapproved comments
  • Remove all unused tags
  • Remove all unused post meta
  • Optimize MySQL tables by removing all unused table space
Note:  For smooth running of your wordpress blog, consider wordpress hosting from BlueHost. BlueHost has web servers optimized for WordPress and is #1 choice of WordPress.org. Visit http://www.bluehost.com