Search

How to Clean Up and Optimize Your WordPress Database

Listen to this article

Optimize WordPress DatabaseAs your WordPress website or blog grows, maintenance becomes a growing concern.

We always want our site to be optimized, free of unwanted and unused data. Well, there are several steps you could follow on a regular basis to optimize your WordPress database and free it of any clutter.

Let’s take a look.

[su_note note_color=”#ffffba”]IMPORTANT: Always backup your database before performing any of the operations.[/su_note]

[space]

Delete Post Revisions

  • Revisions for each blog post are saved so that you can revert to them in case of any issue. But over time, these revisions could take up a lot of space in your database, and are of no importance to you. You can use this query to remove all post revisions and the meta associated with them.

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';
  • Alternatively you could use a plugin, Better Delete Revision. This plugin allows you to delete a certain number of post revisions, so you can save maybe the latest two revisions, and delete the rest.

[space]

Remove Unused Tags

  • Tags which are not used can be removed using the following query.

DELETE wt, wtt 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;
  • Do note, you might want to save some tags for a rainy day. You can alternatively choose to list all unused tags (using SELECT instead of DELETE), and then proceed to deleting individual tags.

[space]

Delete All Pingbacks and Spam Comments at once

  • If you have multiple pages of spam comments and want to delete all comments at once, you could use the following query. This query will also delete pingbacks.
DELETE FROM wp_comments WHERE comment_approved = 'spam' OR comment_type = 'pingback';
  • Delete meta data for removed comments

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

[space]

Delete Unused Tables and Remove Unused Space Between Tables

  • The WPDBSpringClean plugin removes unused tables left behind by uninstalled plugins. You can use this plugin to delete such tables and optimize the database space.

Note: You can alternatively look up the Optimize Database After Deleting Revisions plugin to do most of the above operations.

[space]

Remove Unused Images

As part of your cleanup operations, you would also want to remove unused images from content folder. Unused images can be identified and removed using the DNUI Delete Not Used Image Plugin. This plugin identifies unreferenced images, and lists them, providing you with an option to choose and delete the ones you want. The image data is also removed from the database.

Note: Along with the backup of the database, you would also want to back up your uploads folder, just in case of any error.

[space]

Conclusion

Optimizing your WordPress Database, should be a part of your site maintenance, and should be done on a timely basis.

This article covers a few steps all of us can take as part of cleaning up unwanted data. Maybe there are some more steps, you would like to suggest, or some plugins that you find useful for doing the same. Do let us know by writing to us in the comments section below.

Reference: http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

 

Aparna Gawade

Aparna Gawade

Leave a Reply

Your email address will not be published. Required fields are marked *

Get The Latest Updates

Subscribe to our Newsletter

A key to unlock the world of open-source. We promise not to spam your inbox.

Suggested Reads

How to Make Responsive Tables using CSS without Tag
Join our 55,000+ Subscribers

    The Wisdm Digest delivers all the latest news, and resources from the world of open-source businesses to your inbox.

    Suggested Reads