WordPress Comment Counts are Wrong

I was recently asked by a friend to look at his WordPress blog. His hosting company had a problem which meant they had to do a database restore for his blog, and his comment counts were incorrect after the restore.

After some searching around, I found a few PHP scripts which could be installed server-side to loop through the database rows and recalculate the comment counts, but this seemed very inefficient to me (and I didn’t have quick access to his filesystem, anyway).

Eventually, I came up with this simple MySQL command to rewrite all comment counts correctly. I’m posting it here so I have it if I need it again:

UPDATE wp_posts SET comment_count = (SELECT COUNT(comment_ID) FROM wp_comments WHERE ID = comment_post_id AND comment_approved = 1);