Fixed "Threads with your posts" is very slow

Steffen

Well-known member
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.
Code:
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:
Code:
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:
Code:
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)

Code:
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:
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.
 
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?
 
I've just removed it from "findThreadsWithPostsByUser" because the others (for example "findThreadsWithNoReplies") didn't cause any problems.
 
Back
Top Bottom