A year ago today, I wrote about fighting trackback spam on a self-hosted WordPress site. It involved running a SQL query directly on the database to speed up the clean up process and make it more efficient. Since I was dealing with over 100 spam-linking comments on each post, I could use all the help in that regard I could get. Fortunately, the solution written up there worked out quite well.
Unfortunately, I glossed over a side-effect of the manual clean-up effort. Since I deleted the trackback comments directly from the database, the process did not kick off WordPress’s other functions like updating the comment count on an article. This slight annoyance meant the comment count in an article byline would be extremely inflated over the actual number of comments. I dealt with it for 365 days, through no planning of mine, today I cleaned up the mess with one easy to use query to synchronize the comment count with the actual number of comments received per article. While my problem stemmed from previous modifications taken in the database there are plenty of plugins or other ways for this value to become inaccurate.
I hopped into phpMyAdmin in order to browse the tables, familiarize myself with the database, and see if I could find where the inaccurate number was stored.
Before making changes to your database, I highly recommend making a back up and testing each query.
I actually managed to get lucky and pretty quickly found where the comment count per article was stored. It’s in the ‘posts’ table in a column titled ‘comment_count’. How’s that for obvious? Sure enough, when I looked at the comment_count field for a row, it matched the inaccurate number reported on the article itself. There are a number of columns in the ‘posts’ table, so I did a quick SQL query to just make the table a little more browsable and selected only the fields that were relevant.
[SQL]SELECT ID, post_title, comment_count FROM `wp_posts`[/SQL]
Even if you have permalinks enabled, you can still quickly jump to an article by visiting the page in your browser as yourdomain.com/?p=[ID] so www.404techsupport.com/?p=1804 brings up the AT&T U-Verse post.
The screenshot above shows the ‘posts’ table after I fixed it. Before, the comment_count column each reported 100+ comments. To get to the problematic posts, you might modify the query to include WHERE ‘comment_count’ > 100.
To find out how many comments were posted on a particular post, I performed a different SQL query.
[SQL]SELECT * FROM wp_comments WHERE comment_post_id= ‘706’[/SQL]
This returned each comment attributed to the article with the ID I entered in the query. The line at the top gave me a count of the total number of comments on that post. While it was informative to review all of the comments and revisit the topics of old articles, I was more interested in getting just the facts so I could keep making progress.
A slight modification to the query used the COUNT() function to return the number of comments attributed to a particular post.
[SQL]SELECT COUNT(*) FROM wp_comments WHERE comment_post_id= ‘706’[/SQL]
Now, to tie the two queries together with an update to the posts.comment_count column, this is the query that you need to run to synchronize the comment count with the actual count.
[SQL]UPDATE wp_posts
SET comment_count=( SELECT COUNT(*)
FROM wp_comments
WHERE wp_posts.id=wp_comments.comment_post_id
)[/SQL]
This query worked out quite well for me on WordPress 3.5.1. You will need to replace ‘wp_posts’ and ‘wp_comments’ with the names of your tables in case you have changed your table prefix. The number of rows affected tell you how many posts of yours have any comments.
After running the above query with positive results, you will need to clear your site’s cache before expecting to see the comment count update on an article. Now my site is back in sync and my the comment count on articles will no longer be artificially high and remind me of all the spam I had to clean up.