INNER JOINs are noticeably slower than LEFT JOINs (because they need to actually be looked at for exclusions). This gets *really* slow when you have a forum with hundreds (or thousands) of pages of threads... All the threads from previous pages need to actually perform the join in order to see if they are to be excluded from the list.
In the case of this method, the INNER JOINs are really serving no purpose other than to do a double check on data integrity (don't show threads unless the node exists, don't show threads unless the forum exists, don't show the thread unless it has a first post, etc) Doing data integrity checks at the query level is really not worth the huge amount of resources needed when you start getting into high page numbers.
The other issue in this method is SQL IF() statements are even more resource intensive than the JOINs because they also need to be evaluated for all threads in previous pages. This part in particular:
IF(thread_user_post.user_id IS NULL, 0, thread_user_post.post_count) AS user_post_count
thread_user_post.post_count AS user_post_count