Future Fix Widget latest comments causes slow query

[Kirby]

UBS Version

2.3.8

XenForo Version

2.3.6

PHP Version

8.3.20

Database & Version

MariaDB 10.11.11

Are there any errors being throw?

No

SELECT `xf_xa_ubs_comment`.*, `xf_xa_ubs_blog_entry_BlogEntry_1`.*, `xf_user_User_2`.*, `xf_xa_ubs_category_Category_3`.*, `xf_xa_ubs_blog_Blog_4`.*
FROM `xf_xa_ubs_comment`
LEFT JOIN `xf_xa_ubs_blog_entry` AS `xf_xa_ubs_blog_entry_BlogEntry_1` ON (`xf_xa_ubs_blog_entry_BlogEntry_1`.`blog_entry_id` = `xf_xa_ubs_comment`.`blog_entry_id`)
LEFT JOIN `xf_user` AS `xf_user_User_2` ON (`xf_user_User_2`.`user_id` = `xf_xa_ubs_comment`.`user_id`)
LEFT JOIN `xf_xa_ubs_category` AS `xf_xa_ubs_category_Category_3` ON (`xf_xa_ubs_category_Category_3`.`category_id` = `xf_xa_ubs_blog_entry_BlogEntry_1`.`category_id`)
LEFT JOIN `xf_xa_ubs_blog` AS `xf_xa_ubs_blog_Blog_4` ON (`xf_xa_ubs_blog_Blog_4`.`blog_id` = `xf_xa_ubs_blog_entry_BlogEntry_1`.`blog_id`)
WHERE (`xf_xa_ubs_blog_entry_BlogEntry_1`.`category_id` IN (1)) AND (`xf_xa_ubs_comment`.`comment_state` = 'visible')
ORDER BY `xf_xa_ubs_comment`.`comment_date` DESC, `xf_xa_ubs_comment`.`comment_id` DESC
LIMIT 50

Run Time: 25.316487

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_xa_ubs_blog_entry_BlogEntry_1	range	PRIMARY,category_publish_date,category_last_update,category_rating_weighted	category_publish_date	4		8514	Using index condition; Using temporary; Using filesort
SIMPLE	xf_xa_ubs_comment	ref	blog_entry_id_comment_date,blog_entry_id_comment_state	blog_entry_id_comment_state	5	<redacted>.xf_xa_ubs_blog_entry_BlogEntry_1.blog_entry_id,const	8	Using index condition
SIMPLE	xf_user_User_2	eq_ref	PRIMARY	PRIMARY	4	<redacted>.xf_xa_ubs_comment.user_id	1
SIMPLE	xf_xa_ubs_category_Category_3	const	PRIMARY	PRIMARY	4	const	1
SIMPLE	xf_xa_ubs_blog_Blog_4	eq_ref	PRIMARY	PRIMARY	4	<redacted>.xf_xa_ubs_blog_entry_BlogEntry_1.blog_id	1

XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 96
XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1445
XF\Mvc\Entity\Finder->fetch() in src/addons/XenAddons/UBS/Widget/LatestComments.php at line 75
XenAddons\UBS\Widget\LatestComments->render() in <redacted>/code_cache/widgets/_31_xa_ubs_latest_comments.php at line 5
XF\SubContainer\Widget->{closure}() in src/XF/Template/Templater.php at line 8177
XF\Template\Templater->renderWidgetClosure() in src/XF/SubContainer/Widget.php at line 176
XF\SubContainer\Widget->getCompiledWidget() in src/XF/Template/Templater.php at line 2144
XF\Template\Templater->widgetPosition() in <redacted>/code_cache/templates/l1/s3/public/xa_ubs_category_view.php at line 452
XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 1799
XF\Template\Templater->renderTemplate() in src/XF/Template/Template.php at line 24
XF\Template\Template->render() in src/XF/Mvc/Renderer/Html.php at line 50
XF\Mvc\Renderer\Html->renderView() in src/XF/Mvc/Dispatcher.php at line 471
XF\Mvc\Dispatcher->renderView() in src/XF/Mvc/Dispatcher.php at line 453
XF\Mvc\Dispatcher->renderReply() in src/XF/Mvc/Dispatcher.php at line 412
XF\Mvc\Dispatcher->render() in src/XF/Mvc/Dispatcher.php at line 66
XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2826
XF\App->run() in src/XF.php at line 806
XF::runApp() in index.php at line 23

Category ID #1 has about 220K comments, with this query execution time the widget is (almost) unusable.

Suggested Fix
Add a cutoff setting to limit the amount of possible comments
Store denormalized category_id in xf_xa_ubs_comment (Optional)