This one is for WordPress mulitisite admins out there looking for a way to bulk delete all spam comments across their entire network and reduce database overhead. Tripawds members can feel free to skip the geekspeak.
Why WordPress stores spam comments in the first place is beyond me, but that’s a different discussion. When I discovered our wp_site_comments table was consuming more than a gigabyte of the database for our Tripawds Blogs multisite network, I was happy to find this gem of an SQL query to delete spam comments and modified it slightly to do so from all blogs at once.
DELETE FROM wp_site_comments WHERE comment_approved = 'spam';
In a matter of seconds I reduced the size of our database by more than a gigabyte!
Yes, WordPress does provide the ability to delete spam comments from the dashboard, but not from all sites on a multisite network at once. This simple query allowed me to delete spam from all sites in one batch. By using phpMyAdmin to execute this SQL query the job is completed quickly, efficiently and without the need to visit each site’s dashboard or the memory overhead created when WordPress attempts to process a large number of comments.
It’s that easy!
- Backup your database (Highly Recommended)
- Launch PHP MyAmin
- Load your network’s database
- Click the SQL tab
- Run the query above
- Optimize the wp_site_comments table to remove any overhead
What does this mean to Tripawds Members?
Nothing really. This is just a reminder of our ongoing efforts to keep this free community online. We spend countless hours managing the Tripawds Blogs network and discussion forums to keep everything operating at top performance. Your continuing support helps make this possible. Thank you.