SQL Commands To Help Manage WordPress Comments And Spam

Last month I did a lot of work to make my sites load faster.  One of the easiest ways to improve site performance is to remove unnecessary plugins, and one that I removed was Disqus.

Disqus is a great, sexy looking comment replacement for wordpress that is easy to install but I decided it wasn’t worth all the additional external javascript it was adding to my pages.  My pageview:comments ratio is masssive, with probably 1-2 comments per thousand pageviews, so I couldn’t justify offering a good experience for 2 users and a worse experience for the other 998.

Another reason why I removed Disqus is because for visitors without javascript, all comments are displayed, even spammy trackbacks and pingbacks.  This is really bad, as this is the version of  pages that the googlebot sees.

After removing Disqus, I also realised what a poor job it had been doing with blocking spam so I had to spend some time removing these.  Here are some of the steps I took.

Close All Pings/Trackbacks On Posts

In the wordpress dashboard there is an option to stop all pings and trackbacks.  Unfortunately this only works on posts written after you choose this option.  To apply it to all posts, go into your sql database and run:

UPDATE wp_posts SET ping_status = ‘closed’

This will close pings/trackbacks on all posts.

Mass Delete Pingbacks & Trackbacks

Some may disagree, but I see no value to displaying pingbacks and trackbacks on a post.  Most of these are spam anyway, so to delete all pingbacks and trackbacks:

DELETE FROM wp_comments WHERE comment_type = ‘pingback’

to delete all pingbacks, and:

DELETE FROM wp_comments WHERE comment_type = ‘trackback’

to delete all trackbacks.

The other steps I’m taking, unfortunately I haven’t found an easy way to fix.  Re-importing my comments back to WP has left me with lots of duplicates, which I’m having to delete manually..it’s a long term project…

The other problem is all the spam that Disqus missed.  What I had to do here, was use the updated word block list that akismet uses and search and delete offending comments.  Doing this also highlighted additional words to add to the filters, so I think my site is pretty much spam free now.

To stop any future spam, I’ve also set WP to make pending any comment containing a link.

, , , , , , ,

Comments are closed.