I’m currently working on a university WordPress network that’s been running for four or five years (an MU veteran!) and has almost 5000 blogs, most of which are defunct (because they’re from previous semesters). Akismet is activated across the network, so there’s not much of a public spam problem. However, even spam comments are stored in the database, and some of the blogs have tens of thousands of spam comments sitting in their tables. I’m going to implement a couple of tricks to keep this from happening in the future (a lightweight honeypot for non-logged-in users, tell Akismet to auto-delete spam comments on old posts). But for now, I’ve got to clean up this mess, because the very large comment and commentmeta tables are causing resource issues.
I wrote a simple script that gradually cycles through all the blogs on the network and deletes comments that have been marked as spam by Akismet. Here it is, with some comments afterward:
- The number of blogs is hardcoded (4980)
- The ‘qw_delete_in_progress’ key is a throttle, ensuring that only one of these routines is running at a time. You might call this the poor man’s poor man’s cron.
- I’ve limited it to 10 comments per pageload, but you could change that if you wanted
- Put it in an mu-plugins file. When it’s finished running (check the ‘qw_delete_next_blog’ flag in the wp_sitemeta table – it’s done if it’s greater than the total number of blogs on the system), be sure to remove it, or at least comment out the
Use at your own risk – I’m posting here primarily for my own records 🙂
Pingback: Delete spam from all blogs — WordPress "Must-Use" Tutorials
interesting may come in handy in the future. thanks for sharing!
Very useful function: thanks for posting it!
Pingback: Borrar todos los comentarios spam de una red WordPress descargar gratis | Zonadictz
Awesome Boone. Thanks.
Unless I’m missing something, it doesn’t look like this will clear out the Akismet data in commentmeta… that’s where most of our bloat is happening (Akismet dumps our spam every 30 days, but all of our calls to it get stored in commentmeta). Do you know if there’s a reason for that data to be retained?
wp_delete_comment()does delete commentmeta (which is the main reason I’m using it, rather than doing a much quicker sql query).
Pingback: A Blogger’s Best Friend: Askimet | Inside Out Cafe
Pingback: AKISMET WORDPRESS PLUGIN - ElevatedBlog
Pingback: 500 Posts! | The Ranting Papizilla
Pingback: Akismet….. for the record « Minkyweasel World
Pingback: Akismet WordPress Plugin - ElevatedBlog : ElevatedBlog
Are you still using this successfully? We’re dealing with some similar issues with UMW Blogs and could clear out multiple GB of DB usage if I were able to do this. Let me know!