Fixed "Threads with your posts" is very slow

[Steffen]

Affected version

2.0.4

Our database contains about 1.7 million threads and 20.7 million posts. XenForo seems to be able to cope with this really well with one exception: The feature "Threads with your posts" (https://xenforo.com/community/find-threads/contributed) is very slow. It takes about 4-5 seconds to load whereas all other pages load instantly.

I've enabled the debug mode and found that the reason is a single slow query that takes 4-5 seconds to execute.

SELECT COUNT(*)
FROM `xf_thread` FORCE INDEX (`last_post_date`)
INNER JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_3` ON (`xf_thread_user_post_UserPosts_3`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_3`.`user_id` = '62')
WHERE (`xf_thread`.`discussion_type` <> 'redirect') AND (`xf_thread`.`discussion_state` = 'visible') AND (`xf_thread`.`node_id` IN (...))

Run Time: 4.585378
Select Type    Table    Type    Possible Keys    Key    Key Len    Ref    Rows    Extra
SIMPLE    xf_thread    ALL                        1713087    Using where
SIMPLE    xf_thread_user_post_UserPosts_3    eq_ref    PRIMARY,user_id    PRIMARY    8    xenforo.xf_thread.thread_id,const    1    Using index

XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 79
XF\Db\AbstractAdapter->query() in src/XF/Db/AbstractAdapter.php at line 91
XF\Db\AbstractAdapter->fetchOne() in src/XF/Mvc/Entity/Finder.php at line 1118
XF\Mvc\Entity\Finder->total() in src/XF/Pub/Controller/FindThreads.php at line 79
XF\Pub\Controller\FindThreads->getThreadResults() in src/XF/Pub/Controller/FindThreads.php at line 58
XF\Pub\Controller\FindThreads->actionContributed() in src/XF/Mvc/Dispatcher.php at line 249
XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 88
XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 41
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 1905
XF\App->run() in src/XF.php at line 328
XF::runApp() in index.php at line 13

(I have abbreviated the list of node ids in the where clause of the query with "...").

When I run this query manually I can confirm that it is indeed slow:

mysql> SELECT COUNT(*) FROM `xf_thread` FORCE INDEX (`last_post_date`) INNER JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_3` ON (`xf_thread_user_post_UserPosts_3`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_3`.`user_id` = '62') WHERE (`xf_thread`.`discussion_type` <> 'redirect') AND (`xf_thread`.`discussion_state` = 'visible') AND (`xf_thread`.`node_id` IN (...));
+----------+
| COUNT(*) |
+----------+
|     4240 |
+----------+
1 row in set (4,23 sec)

The problem goes away if I remove the "FORCE INDEX (`last_post_date`)" part of the query:

mysql> SELECT COUNT(*) FROM `xf_thread` INNER JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_3` ON (`xf_thread_user_post_UserPosts_3`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_3`.`user_id` = '62') WHERE (`xf_thread`.`discussion_type` <> 'redirect') AND (`xf_thread`.`discussion_state` = 'visible') AND (`xf_thread`.`node_id` IN (...));
+----------+
| COUNT(*) |
+----------+
|     4240 |
+----------+
1 row in set (0,02 sec)

mysql> explain SELECT COUNT(*) FROM `xf_thread` INNER JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_3` ON (`xf_thread_user_post_UserPosts_3`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_3`.`user_id` = '62') WHERE (`xf_thread`.`discussion_type` <> 'redirect') AND (`xf_thread`.`discussion_state` = 'visible') AND (`xf_thread`.`node_id` IN (...));
+----+-------------+---------------------------------+--------+---------------------------------------------------------------+---------+---------+-------------------------------------------+------+-------------+
| id | select_type | table                           | type   | possible_keys                                                 | key     | key_len | ref                                       | rows | Extra       |
+----+-------------+---------------------------------+--------+---------------------------------------------------------------+---------+---------+-------------------------------------------+------+-------------+
|  1 | SIMPLE      | xf_thread_user_post_UserPosts_3 | ref    | PRIMARY,user_id                                               | user_id | 4       | const                                     | 4266 | Using index |
|  1 | SIMPLE      | xf_thread                       | eq_ref | PRIMARY,node_id_last_post_date,node_id_sticky_state_last_post | PRIMARY | 4       | xf_thread_user_post_UserPosts_3.thread_id |    1 | Using where |
+----+-------------+---------------------------------+--------+---------------------------------------------------------------+---------+---------+-------------------------------------------+------+-------------+
2 rows in set (0,00 sec)

diff --git a/src/XF/Repository/Thread.php b/src/XF/Repository/Thread.php
index e91bd52a6..a1bd1f61e 100644
--- a/src/XF/Repository/Thread.php
+++ b/src/XF/Repository/Thread.php
@@ -83,8 +83,7 @@ class Thread extends Repository
             ->with(['Forum', 'User'])
             ->exists('UserPosts|' . $userId)
             ->where('discussion_type', '<>', 'redirect')
-            ->setDefaultOrder('last_post_date', 'DESC')
-            ->indexHint('FORCE', 'last_post_date');
+            ->setDefaultOrder('last_post_date', 'DESC');
     }

     public function findThreadsWithNoReplies()

I think this patch also removes the index hint from the real query, i.e. the non-count(*)-query. For us, this does not seem to cause any harm. I'm using MySQL 5.6.39.

 

[Kier]

I'm experimenting with your change - have you tried removing the index hint from the other findThreadsWith... functions, or is it just this one that has performance issues for you?

 

[Steffen]

I've just removed it from "findThreadsWithPostsByUser" because the others (for example "findThreadsWithNoReplies") didn't cause any problems.

 

[Kier]

Okay, I've committed your fix, let's see how it goes...