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:

Notes:

• 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 register_shutdown_function line.

Use at your own risk – I’m posting here primarily for my own records 🙂