As 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/
