Working on a project with more than 200k spam comments in the WP database. Trying to use the “empty spam” button in WordPress just doesn’t work, it deletes a few tens of thousands before hitting the 30 second PHP timeout.
I searched around and found a few suggestions to delete from the wp_comments table. However, that ignores the 3x as many rows in the wp_commentsmeta table if you’re using akismet. This query does the job, although for 200k comments it does take a while!
DELETE c, m FROM wp_comments AS c LEFT JOIN wp_commentmeta AS m ON m.comment_ID = c.comment_ID WHERE c.comment_approved = 'spam';