Managing Movable Type Comment Spam With Database Queries

For this blog, any comment that Akismet believes to be spam gets flagged as junk. Akismet catches about 60-70% of all spam posted here. All of the remaining spam posts get flagged as "moderated" by ModerateViaDelay, which assumes that comments on old entries that haven't seen any comments in a while are probably junk. Moderated comments do not show up on the site until I approve them, which means no comment spam ever makes it to the site. I make periodic sweeps through the moderated comments to publish the occasional legitimate comment posted to an old entry, and re-flag everything else as junk.

Movable Type's administrative interface makes it easy to apply a single action, such as "flag as junk," to up to 125 comments at a time. However, I usually have to clean out several thousand moderated comments in one sitting. I'd much prefer a "mark all moderated as junk" button. Until I get around to writing a plugin to add such a button (or until someone tells me there already is one), here's a MySQL query that moves all moderated comments into junk status with MT 3.34:

update mt_comment set comment_junk_status = -1 where comment_visible = 0;

Movable Type is supposed to be able to delete junk messages older than 7 days, if set up correctly. Either my set-up hasn't been doing this, or I get tons of comment spam, but I had over 31,000 junk messages in my database one recent evening. Unfortunately, that's enough to overload the "delete all junk" button in the MT admin interface on my cheap web hosting. Here's a MySQL query that does the same thing (which took 55 seconds for me—like I said, cheap web hosting):

delete from mt_comment where comment_junk_status = -1

Keeping the comment posting form open on old entries wouldn't be worth all the effort, bandwidth and disk space, except for the fact that 75% of the visitors to this site are people finding old entries from search engines. The two or three "thank you for posting the solution to this obscure technical problem" comments I get in a year make it all worthwhile. Though I enjoy comments from regular readers as well. :)