5 WordPress MySQL Queries For Year Ending Summary Posts & Cleanup

Wordpress Logo Love Below I am posting some MySql queries for power WordPress bloggers. This queries will come handy if you are planning to write annual summary and analysis posts like most bloggers do at the end of year.

All queries are direct SQL and assumes standard table prefix ‘wp_’ for tables. If you are using different table prefix, please modify queries accordingly.

We strongly suggest you to back up your database as we will not be responsible for any loss or damage caused by using our code directly or indirectly. I also assume you know PhpMyAdmin or any other interface to execute MySql queries, because if you send me any question like how to use following MySql queries, I will simply reply – “Google it!” 😉

I guess I have scared you enough by now, so enjoy real stuff before you make your mind to go away…

Yearly Stats…

To get number of posts published in entire year…

SELECT * FROM `wp_posts` WHERE `post_date` <= '2008-12-31 23:59:59' AND `post_date` >= '2008-01-01 00:00:00' AND `post_status` = 'publish'

To get number of comments approved in entire year…

SELECT * FROM `wp_comments`  WHERE `comment_date` <= '2008-12-31 23:59:59' AND  `comment_date` >= '2008-01-01 00:00:00' AND `comment_approved` = 1 AND `comment_type` = ''

Change comment_type equal to ‘pingback’ or ‘trackback’ to get number of pingbacks and trackback respectively.

To get number of users registered in entire year…

SELECT * FROM `wp_users`  WHERE `user_registered` <= '2008-12-31 23:59:59' AND  `user_registered` >= '2008-01-01 00:00:00'

This is useful for only blogs who allows user to register, like Devils Workshop!

WARNING: Following query performs WRITE operation on database!

Cleanup Time!

Starting from WordPress 2.5, a revision featured was introduced which eats up lots of space in database. In case you want to perform some clean-up which is good idea, you can shoot following query!

To Delete Post Revision from WordPress Database…

DELETE FROM `wp_posts` WHERE `post_type` = ‘revision’ AND `post_date` <= '2008-12-20 23:59:59'

This will delete all post revisions created before December 21, 2008. This is to retain revisions created in last 10 days. If you remove AND post_date <= ‘2008-12-20 23:59:59’ from above query it will delete all revisions.

On executing this query, it removed 758 revisions from our database. I forgot to calculate how much space it freed, but its always good to have unwanted data out of database.

To Delete All Comments awaiting moderation…

Yes, this may sound crazy, but if for some reason your blog has been hit by comment spam, just when you forgot to put anti-spam measures in place, you must be needing something like. Its really good idea delete all unmoderated comment for one-time so you can start-over again… 🙂

DELETE from `wp_comments` WHERE `comment_approved` = '0';

You can also add DATE range to above query, in case you have idea about when comment spam hit your blog.

Thats All! If you want any particular MySQL query, please send details via following comments. 🙂

Related: WordPress Plugin Series – Reviews of Top WordPress Plugins

(image credit: Design Simply)


Abhishek December 30, 2008

Thanks mate !! nice way to say bye 2008

p@r@noid December 31, 2008

Cool tip mate. Will give it a try though I’m no good with php!

Shirley January 2, 2009

Useful queries. I am doing a bit of maintenance, inspired by Abhishek, to get rid of needless database ‘bloat’.

Rahul Bansal January 3, 2009

@Abhishek, @p@r@noid & @Shirley
Glad to know that you find this post helpful! 🙂

Abhishek January 3, 2009

Rahul due to this tip I opened my PHPMYADMIN and cleaned near about 31 MB of DB. Initiative credit to this post !!

Happy New Year To All Devils !!

Rahul Bansal January 7, 2009

31MB is really considerable amount. I forgot to take note of size before starting cleaning operation so I cant say how much I have space I have freed here at DW.
Wish u happy new year to you as well… 🙂

Abhishek January 7, 2009

Thanks Rahul !!

Chaya October 3, 2009

Seems like you really took your time on this. Keep up the good work! 🙂

Vivek Parmar October 23, 2010

thanks for this article, as i only knows about post revision and working on to disable it. now i would clean up all.
thanks again