XenForo Recalculate and update all user post counts (query)

This will recalculate and update the post counts for all users at once. This query can be slow on large forums with lots of posts:

Code:

UPDATE xf_user AS user
SET message_count = (
 SELECT COUNT(*)
 FROM xf_post AS post
 LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
 WHERE post.user_id = user.user_id
 AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
 GROUP BY post.user_id
);

If you have forums you wish to exclude from the count then use this query, where 1,2,3 is a comma-list of node_ids that are excluded from the count:

Code:

UPDATE xf_user AS user
SET message_count = (
 SELECT COUNT(*)
 FROM xf_post AS post
 LEFT JOIN xf_thread AS thread ON (thread.thread_id = post.thread_id)
 WHERE post.user_id = user.user_id
 AND thread.node_id NOT IN (1,2,3)
 AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
 GROUP BY post.user_id
);