XF 2.2 Database performance issues after upgrading to 2.2


we recently updated our forum to XenForo 2.2. In one of our self-written add-ons, we list the 25 newest threads from the database. The add-on code looks like this:

$maxThreads = 25; $visitor = \XF::visitor(); $repository = $this->repository('XF:Thread'); $withPermission = sprintf('Forum.Node.Permissions|%d', $visitor->permission_combination_id); $threadList = $repository ->findLatestThreads() ->with($withPermission, true) ->limit($maxThreads) ->fetch() ->filterViewable();

This piece of code unfolds to this query:

SELECT `xf_thread`.*, `xf_forum_Forum_1`.*, `xf_node_Node_2`.*, `xf_user_User_3`.*, `xf_permission_cache_content_Permissions_4`.* FROM `xf_thread` INNER JOIN `xf_forum` AS `xf_forum_Forum_1` ON (`xf_forum_Forum_1`.`node_id` = `xf_thread`.`node_id`) INNER JOIN `xf_node` AS `xf_node_Node_2` ON (`xf_node_Node_2`.`node_id` = `xf_forum_Forum_1`.`node_id`) LEFT JOIN `xf_user` AS `xf_user_User_3` ON (`xf_user_User_3`.`user_id` = `xf_thread`.`user_id`) INNER JOIN `xf_permission_cache_content` AS `xf_permission_cache_content_Permissions_4` ON (`xf_permission_cache_content_Permissions_4`.`content_type` = ? AND `xf_permission_cache_content_Permissions_4`.`content_id` = `xf_node_Node_2`.`node_id` AND `xf_permission_cache_content_Permissions_4`.`permission_combination_id` = ?) WHERE (`xf_thread`.`discussion_state` = ?) AND (`xf_thread`.`discussion_type` <> ?) ORDER BY `xf_thread`.`post_date` DESC LIMIT 25

This query causes a huge performance issue in our forum with roundabout 50,000 threads, 1,000,000 posts and more than 40,000 users.

In XenForo 2.1.5, we didn't see any performance problems. Do you have any idea what we can improve here?

Top Bottom