This is a query which updates user post counts. It takes into consideration moderated and deleted status of threads and posts, as well as each forum's "Count messages posted in this forum toward user total" setting.

Code: 
UPDATE xf_user AS user SET message_count =
COALESCE(
 (
        SELECT COUNT(*)
        FROM xf_post AS post
        INNER JOIN xf_thread AS thread ON thread.thread_id = post.thread_id
        INNER JOIN xf_forum AS forum ON forum.node_id = thread.node_id
        WHERE post.user_id = user.user_id AND post.message_state = 'visible'
        AND thread.discussion_state = 'visible' AND forum.count_messages = 1
        GROUP BY post.user_id, 0
), 0 );

The above single query may timeout on very large forums. If this is the case, you can break it down into queries that handle a smaller number of users in each query.

The example below would handle it in three chunks of up to 50,000 users with each query. Change it as needed for your forum and keep running it with additional queries, each with higher counts in the two user.user_id WHERE clauses until no more records are matched:

Code: 
UPDATE xf_user AS user
SET message_count =
     COALESCE(
     (
        SELECT COUNT(*)
        FROM xf_post AS post
        INNER JOIN xf_thread AS thread ON thread.thread_id = post.thread_id
        INNER JOIN xf_forum AS forum ON forum.node_id = thread.node_id
        WHERE post.user_id = user.user_id
        AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
        AND forum.count_messages = 1
        AND user.user_id > 0 AND user.user_id < 50001
        GROUP BY post.user_id
    ), 0 );

UPDATE xf_user AS user
SET message_count =
     COALESCE(
     (
        SELECT COUNT(*)
        FROM xf_post AS post
        INNER JOIN xf_thread AS thread ON thread.thread_id = post.thread_id
        INNER JOIN xf_forum AS forum ON forum.node_id = thread.node_id
        WHERE post.user_id = user.user_id
        AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
        AND forum.count_messages = 1
        AND user.user_id > 50000 AND user.user_id < 100001
        GROUP BY post.user_id
    ), 0 );

UPDATE xf_user AS user
SET message_count =
     COALESCE(
     (
        SELECT COUNT(*)
        FROM xf_post AS post
        INNER JOIN xf_thread AS thread ON thread.thread_id = post.thread_id
        INNER JOIN xf_forum AS forum ON forum.node_id = thread.node_id
        WHERE post.user_id = user.user_id
        AND post.message_state = 'visible' AND thread.discussion_state = 'visible'
        AND forum.count_messages = 1
        AND user.user_id > 100000 AND user.user_id < 150001
        GROUP BY post.user_id
    ), 0 );