We recently decided to clear out old comments on blog entries on one of our WordPress sites. We have almost six years worth of comments and data on this particular blog, most of it pertaining to old news items. While the discussions were interesting and engaging in their day, they now represent tens – maybe hundreds – of thousands of lines of database entries.
Data purists may shriek in horror, but we decided to prune these old comments rather than keep them for what little historical value they had. There’s a fairly handy line of MySQL code that can be run to delete old comments:
delete from wp_comments where date_sub(now(), interval 360 day) > comment_date ORDER BY comment_date ASC LIMIT 4000;
This essentially takes any comment more than a year old and deletes it. I’ve added a LIMIT=4000 clause to the end to ensure that I don’t bite off more data than I can chew. I initially set this to a smaller value like 100 and kept cross-checking against the comments page in WordPress to make sure I wasn’t causing problems.
I’m not putting this forward as the cleanest method of removing old comments from a WordPress installation, but it’s what I’m currently using. I note that it doesn’t address the commentmeta tables, which presumably hold some data pertaining to the comments we just deleted.
Perhaps the most sensible thing to do is to write a plugin for this which retrieves an array of comment IDs which are eligible for deletion (with a user-configurable date threshold?) and use the wp_delete_comment function to remove the comments in a cleaner manner.
Just a thought.
Disclaimer: I’m not entirely sure that what I’m doing here is safe for your WordPress installation. Make sure you’ve got a solid database backup before you attempt this! And if any WordPress experts are reading this – please chip in with your own solutions.