There’s an awesome WordPress plugin called Comment Notifier – what it does is add that check box at the bottom of the comments section. If you leave it checked when you add a comment, then it will automatically email you with anyone ease’s comments in the future.
However, it has a slight problem with spam. When spammers leave comments, my combination of Akismet and NoSpamNX do a pretty good job of keeping spam comments of of the site, but not before their (usually fake) email gets added to the Comment Notifier database.
Recently, I realized that my server was trying to send out several hundred failing emails any time someone left a comment. I shot a short feature request (and a small donation) to the Comment Notifier plugin’s author, but then decided that this was one I could take on myself. Here’s how I did it:
Step 1: BACK UP YOUR DATABASE
I’m using the BackUpWordPress so this happens automatically, but I went and ran an extra backup and downloaded the files to my laptop just to be safe.
Step 2: Clean up the old data
This SQL query deletes every email in the comment_notifier table that doesn’t have a corresponding comment. (Most likely because the comment was already deleted as spam.)
DELETE FROM `wp_YOUR_PREFIX_comment_notifier` WHERE email NOT IN (
SELECT comment_author_email FROM `wp_YOUR_PREFIX_comments`
);
Obviously, you’ll need to change the table name prefix (wp_YOUR_PREFIX_) to whatever your install of WP uses.
Step 3: Add an index on the comments.comment_author_email column
This is required in order to add the Foreign Key constraint in next step. It makes MySQL keep a sorted list of emails in the comments table and automatically update it any time a comment is added or deleted.
ALTER TABLE `wp_YOUR_PREFIX_comments` ADD INDEX ( `comment_author_email` );
Step 4: Add a Foreign Key constraint that automatically deletes spammers from the comment_notifier table
This instructs MySQL to enforce a link between the comment_notifier table and the comments table so that any email address in the comment_notifier table must also be in the comments table, and if it gets deleted from the comments table, then automatically delete it from the comment_notifier table also.
ALTER TABLE `wp_YOUR_PREFIX_comment_notifier`
ADD CONSTRAINT `auto_delete_spammers`
FOREIGN KEY (`email`)
REFERENCES `wp_YOUR_PREFIX_comments` (`comment_author_email`)
ON DELETE CASCADE;
Step 5: Relax and enjoy 
Any time a comment is deleted as spam (or for any other reason), your Comment Notifier will no longer try to send emails to that comment’s author. Your email server will thank you.