Fixed Unsolved filter for question-type forum very slow

Affected version
2.2 RC2
I've upgraded a clone of our forum to XF 2.2 RC2 and converted from https://xenforo.com/community/resources/question-threads.5767/ using the converted it provides to native XenForo question type.

The forum shows these links:
Latest updates Popular Newest Unanswered Unsolved Your questions Your answers

When clicking Unsolved, the page loads with very noticeable delay - consistently taking over 3 seconds. All other pages always take around 200-250 ms.
That forum has 90K threads / 500K messages, only 4K threads are "solved", so by filtering for "Unsolved", it does have a few to go through.

I'm guessing either the converter is doing something wrong, or some queries here there are not optimized. Remembering vB's Calendar feature, it's going to be a real problem if it stays that way and some "helpful" users notice what's going on.
 
This is mostly due to missing index in xf_thread_question. Try running:
SQL:
ALTER TABLE `xf_thread_question` ADD INDEX `solution_post_id` (`solution_post_id`);
 
An index on solution_post_id won't do anything (unless MySQL is approaching this query badly) as the useful index is the primary key (thread_id). In theory, a covering index on (thread_id, solution_post_id) could give a benefit in reducing index->data lookups, though I don't know how beneficial this would be in the particular case.

While our support forum is smaller (23K threads), the relevant queries seem to run within a couple hundreths of a second max, so jumping up to a few seconds would be a bit unexpected, so it might point to MySQL configuration changes being necessary (to ensure the data is in memory). Saying that, the date limit options on forums are there pretty specifically to improve performance, so that might be relevant here.

Either way, if possible, it's worth temporarily enabling debug mode, going to the slow page and clicking the output time shown in the footer to see debug information. Can you show the queries that are particularly slow, along with the details in the table shown below?
 

Page Time: 2.6893s​

Memory: 4.5296 MB (Peak: 4.8454 MB)​

Queries (13, time: 2.6102s, 97.1%)​


Mike, I've enabled the debug mode and one query stands out as consuming most of that time (within 2.6-3.2s range):

SQL:
SELECT `xf_thread`.*, `xf_deletion_log_DeletionLog_1`.*, `xf_user_User_2`.*, `xf_user_LastPoster_3`.*, `xf_thread_read_Read_4`.*, `xf_thread_user_post_UserPosts_5`.*, `xf_thread_watch_Watch_6`.*
FROM `xf_thread`
LEFT JOIN `xf_deletion_log` AS `xf_deletion_log_DeletionLog_1` ON (`xf_deletion_log_DeletionLog_1`.`content_type` = 'thread' AND `xf_deletion_log_DeletionLog_1`.`content_id` = `xf_thread`.`thread_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_thread`.`user_id`)
LEFT JOIN `xf_user` AS `xf_user_LastPoster_3` ON (`xf_user_LastPoster_3`.`user_id` = `xf_thread`.`last_post_user_id`)
LEFT JOIN `xf_thread_read` AS `xf_thread_read_Read_4` ON (`xf_thread_read_Read_4`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_read_Read_4`.`user_id` = '1')
LEFT JOIN `xf_thread_user_post` AS `xf_thread_user_post_UserPosts_5` ON (`xf_thread_user_post_UserPosts_5`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_user_post_UserPosts_5`.`user_id` = '1')
LEFT JOIN `xf_thread_watch` AS `xf_thread_watch_Watch_6` ON (`xf_thread_watch_Watch_6`.`thread_id` = `xf_thread`.`thread_id` AND `xf_thread_watch_Watch_6`.`user_id` = '1')
LEFT JOIN `xf_thread_question` AS `xf_thread_question_Question_7` ON (`xf_thread_question_Question_7`.`thread_id` = `xf_thread`.`thread_id`)
WHERE (`xf_thread`.`node_id` = 16) AND ((`xf_thread`.`discussion_state` IN ('visible', 'deleted', 'moderated'))) AND (`xf_thread_question_Question_7`.`solution_post_id` = 0) AND (`xf_thread`.`sticky` = 0)
ORDER BY `xf_thread`.`last_post_date` DESC

LIMIT 20
Run Time: 2.393222
Select TypeTableTypePossible KeysKeyKey LenRefRowsExtra
SIMPLExf_thread_question_Question_7ALLPRIMARY102589Using where; Using temporary; Using filesort
SIMPLExf_threadeq_refPRIMARY,node_id_last_post_date,node_id_sticky_state_last_post,node_id_sticky_state_vote_scorePRIMARY4test.xf_thread_question_Question_7.thread_id1Using where
SIMPLExf_deletion_log_DeletionLog_1eq_refPRIMARYPRIMARY31const,test.xf_thread_question_Question_7.thread_id1Using where
SIMPLExf_user_User_2eq_refPRIMARYPRIMARY4test.xf_thread.user_id1
SIMPLExf_user_LastPoster_3eq_refPRIMARYPRIMARY4test.xf_thread.last_post_user_id1
SIMPLExf_thread_read_Read_4eq_refuser_id_thread_id,thread_iduser_id_thread_id8const,test.xf_thread_question_Question_7.thread_id1
SIMPLExf_thread_user_post_UserPosts_5eq_refPRIMARY,user_idPRIMARY8test.xf_thread_question_Question_7.thread_id,const1
SIMPLExf_thread_watch_Watch_6eq_refPRIMARY,thread_id_email_subscribePRIMARY8const,test.xf_thread_question_Question_7.thread_id1

  1. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
  2. XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1379
  3. XF\Mvc\Entity\Finder->fetch() in src/XF/Pub/Controller/Forum.php at line 247
  4. XF\Pub\Controller\Forum->actionForum() in src/XF/Mvc/Dispatcher.php at line 350
  5. XF\Mvc\Dispatcher->dispatchClass() in src/XF/Mvc/Dispatcher.php at line 257
  6. XF\Mvc\Dispatcher->dispatchFromMatch() in src/XF/Mvc/Dispatcher.php at line 113
  7. XF\Mvc\Dispatcher->dispatchLoop() in src/XF/Mvc/Dispatcher.php at line 55
  8. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2300
  9. XF\App->run() in src/XF.php at line 464
  10. XF::runApp() in index.php at line 20

Btw, I've tried to click "Remove formatting" (first icon in the new editor) to make the first three lines of my post "normal", not formatted like the source I copied it from and it doesn't seem to work. Is it me? I left it there so you may try by editing my post.

After taking this debug info, I've tried adding the index @TickTackk posted, and it unfortunately did not change a thing.

I also rebuilt forums and threads cache, to no avail.
 
Last edited:
Yup, this is MySQL using a stupid query plan -- we might need to give it a hint, though the difficulty is that there are a couple relevant indexes here, as for the average user, we can use a more specific index.

Would you mind confirming if this is as slow as a guest user (or as a non-privileged user)? They won't be able to see moderated/deleted threads which may change MySQL's query plan.

Also to confirm, what version of MySQL are you using? (Or MariaDB version, if that's what you're using.)
 
Would you mind confirming if this is as slow as a guest user (or as a non-privileged user)? They won't be able to see moderated/deleted threads which may change MySQL's query plan.
As guest the problem still exists, although page loads slightly faster: 1.9-2.2 sec. Probably due to the invisible threads being filtered out.

Also to confirm, what version of MySQL are you using? (Or MariaDB version, if that's what you're using.)
Server version: 10.5.3-MariaDB-1:10.5.3+maria~bionic

Config (on top of docker image mariadb:10.5):
Code:
[mysqld]
performance_schema

innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
tmp_table_size = 256M
max_heap_table_size = 256M

query_cache_size=0 # mutex issues with many cores

# zfs
innodb_flush_log_at_trx_commit=2
skip-innodb_doublewrite
 
Would you mind running these two queries to see if that changes the results?

Code:
ANALYZE TABLE xf_thread;
ANALYZE TABLE xf_thread_question;

This should rebuild index stats and cardinality information. It's possible that is out of date and that's contributing to the bad query plan.
 
Thank you for reporting this issue, it has now been resolved. We are aiming to include any changes that have been made in a future XF release (2.2.8).

Change log:
When filtering for unsolved questions, encourage MySQL to use a better index
There may be a delay before changes are rolled out to the XenForo Community.
 
Top Bottom