I skimmed over it... it looks like they are using LEFT JOIN for records that may or may not be there (things like if the thread was read or not, if the forum was read or not, deletion logs, etc.)
The INNER JOINs are used on stuff that *should* be there, but if parts are not there, you have a data integrity problem so the INNER JOIN essentially hides threads that have data integrity problems.
I ended up using this method to extend the default [plain]XenForo_Model_Thread::prepareThreadFetchOptions()[/plain] method... optimizes the query without needing to edit the files:
[php]
public function prepareThreadFetchOptions(array $fetchOptions)
{
$response = parent::prepareThreadFetchOptions($fetchOptions);
$response['joinTables'] = str_replace ('INNER JOIN', 'LEFT JOIN', $response['joinTables']);
$response['selectFields'] = str_replace (array('IF(thread_user_post.user_id IS NULL, 0, thread_user_post.post_count) AS user_post_count', 'IF(user.username IS NULL, thread.username, user.username) AS username'), array('thread_user_post.post_count AS user_post_count', 'thread.username AS username'), $response['selectFields']);
return $response;
}
[/php]
When it comes down to it, INNER JOIN should never be used on large sets of data, as it simply can't scale well (since you have to actually perform the JOIN on every record before the record you are actually looking at). IF() calculations don't scale well either because the records all need to be evaluated (and dumped to a temporary table) for every record up to the one you are looking at in case you are doing an evaluation of the result of the calculation.
While there are cases where you can't avoid using INNER JOIN (hopefully just on small sets of data), using it for full data integrity checking every time you are getting records is just not a good idea if you want to be able to scale out.
Maybe I'll put together an article (or add-on) that optimizes stuff like I did for vBulletin 4 (see this).
