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.
(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:
The problem goes away if I remove the "FORCE INDEX (`last_post_date`)" part of the query:
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'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.
