Life Saving SQL Queries for a WordPress Blogger
Often its very tedious and time wasting job to clean up the mess on your blog! I find it very irritating at times when several unwanted work eat up my precious time as a webmaster that I could have otherwise used for creating meaningful content to share on my blog. Various tasks that could consume your valuable time as a blogger are:
- Cleaning up Spam / pending comments
- Removing unwanted post revision
- Deleting pending posts created by Spam bots
- Clearing web server cache etc.
- The list simply goes on.
As a programmer / webmaster, I feel that there is always a scope for improvement and better utilization of our time and one classic example is by replacing your long hours of manual works with the help of SQL queries that can get the chore done in few seconds. Today I am sharing some “life saving” SQL queries that I run from the PHPadmin of my web hosting control panel on my Blog’s database to get the work done in seconds and hope these could be useful to you as well.
Empty your WordPress Blog comments “Trash”
DELETE FROM wp_posts WHERE post_status = ‘trash’
Delete Unapproved or Pending comments at once
DELETE from wp_comments WHERE comment_approved = ‘0’;
Delete SPAM comments from your WordPress Blog at once
DELETE FROM wp_comments WHERE wp_comments.comment_approved = ‘spam’
Get a list of all email IDs of the comment authors on your Blog
SELECT DISTINCT comment_author_email FROM wp_comments;
Delete all the post revisions automatically stored on your Blog
Delete Post revisions
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’
One of my Favorite and Special Query: Used to delete all pending posts within a range of date that can be specified in the query. This is most useful to websites that allow user generated content (like directories, classifieds websites etc.)
DELETE FROM wp_posts WHERE post_date < ‘2011-11-06 19:18:00’ AND post_status = ‘pending’
The best part of using queries are that they do not leave any load on your web server and it never happens that your blog crashes down when there are huge number of comments or posts to clean up. Sometimes i have even had to delete as much as 50,000 comments and it gets done in like 3- 4 seconds.
Disclaimer: Even though I personally use all these queries as it is, you might want to make sure you have a backup before playing around with your production website unless you exactly know what you are doing! Hope these queries help you out, let me know what you think.