We've created different forums on our site and one of them contains around 1.5M threads. And we do have a threads per page limit is 25. So, it'll have total pages around 62000. Now, there's a problem, when you go deep into pages like say I have accessed 10000th page, the query which is prepared for this page is taking literally hundreds of seconds to execute:
Please note that we've also 115000 users in xf_user table too. As you can see in the above query, to just read 25 rows, it has examined almost 5M rows!!
As you can see the OFFSET starts very deep. This is Core XF stuff, so I believe indexes are already created properly? Is there anything I could do? Since, this increases a LOT OF load on my AWS DB server and my DB instance is like 100%.
Any help appreciated.
SQL:
# Query_time: 972.392221 Lock_time: 0.000087 Rows_sent: 25 Rows_examined: 4717670
SELECT `xf_thread`.*, `xf_user_User_1`.*, `xf_user_LastPoster_2`.*
FROM `xf_thread`
LEFT JOIN `xf_user` AS `xf_user_User_1` ON (`xf_user_User_1`.`user_id` = `xf_thread`.`user_id`)
LEFT JOIN `xf_user` AS `xf_user_LastPoster_2` ON (`xf_user_LastPoster_2`.`user_id` = `xf_thread`.`last_post_user_id`)
WHERE (`xf_thread`.`node_id` = 2) AND ((`xf_thread`.`discussion_state` IN ('visible'))) AND (`xf_thread`.`sticky` = 0)
ORDER BY `xf_thread`.`last_post_date` DESC
LIMIT 25 OFFSET 1029575;Please note that we've also 115000 users in xf_user table too. As you can see in the above query, to just read 25 rows, it has examined almost 5M rows!!
As you can see the OFFSET starts very deep. This is Core XF stuff, so I believe indexes are already created properly? Is there anything I could do? Since, this increases a LOT OF load on my AWS DB server and my DB instance is like 100%.
Any help appreciated.
Last edited:
