Fixed N+1 query behaviour for article forums

[Xon]

Affected version

2.2.2

In an article forum there are two possible N+1 query patterns;

1. If a thread/first post is bookmarked
2. If the first post's user is permitted for trusted links.

Spoiler: Stacktrace - bookmark

SELECT xf_bookmark_item.*
FROM xf_bookmark_item

WHERE (xf_bookmark_item.content_type = 'post') AND (xf_bookmark_item.content_id = 9169317) AND (xf_bookmark_item.user_id = 7)
ORDER BY xf_bookmark_item.bookmark_date ASC

LIMIT 1
Run Time: 0.000274

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE								Impossible WHERE noticed after reading const tables

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 1346
3. XF\Mvc\Entity\Finder->fetchOne() in src/XF/Mvc/Entity/FinderCollection.php at line 61
4. XF\Mvc\Entity\FinderCollection->offsetGet() in src/XF/Mvc/Entity/FinderCollection.php at line 111
5. XF\Mvc\Entity\FinderCollection->offsetExists() in src/XF/Entity/BookmarkTrait.php at line 43
6. XF\Entity\Post->isBookmarked()
7. call_user_func_array() in src/XF/Template/Templater.php at line 1166
8. XF\Template\Templater->method() in internal_data/code_cache/templates/l1/s25/public/bookmark_macros.php at line 21
9. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 799
10. XF\Template\Templater->callMacro() in internal_data/code_cache/templates/l1/s25/public/post_macros.php at line 326
11. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 799
12. XF\Template\Templater->callMacro() in internal_data/code_cache/templates/l1/s25/public/post_article_macros.php at line 79
13. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 799
14. XF\Template\Templater->callMacro() in internal_data/code_cache/templates/l1/s25/public/forum_view_type_article.php at line 144
15. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 799
16. XF\Template\Templater->callMacro() in internal_data/code_cache/templates/l1/s25/public/forum_view_type_article.php at line 54
17. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 962
18. XF\Template\Templater->renderExtensionInternal() in src/XF/Template/Templater.php at line 898
19. XF\Template\Templater->renderExtension() in internal_data/code_cache/templates/l1/s25/public/forum_view.php at line 641
20. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 1626
21. XF\Template\Templater->renderTemplate() in src/XF/Template/Templater.php at line 1617
22. XF\Template\Templater->renderTemplate() in src/XF/Template/Template.php at line 24
23. XF\Template\Template->render() in src/XF/Mvc/Renderer/Html.php at line 48
24. XF\Mvc\Renderer\Html->renderView() in src/XF/Mvc/Dispatcher.php at line 458
25. XF\Mvc\Dispatcher->renderView() in src/XF/Mvc/Dispatcher.php at line 440
26. XF\Mvc\Dispatcher->renderReply() in src/XF/Mvc/Dispatcher.php at line 400
27. XF\Mvc\Dispatcher->render() in src/XF/Mvc/Dispatcher.php at line 58
28. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2300
29. XF\App->run() in src/XF.php at line 488
30. XF::runApp() in index.php at line 20

Spoiler: Stacktrace - trusted link

SELECT cache_value
FROM xf_permission_combination
WHERE permission_combination_id = ?
Params: 1787
Run Time: 0.000210

Select Type	Table	Type	Possible Keys	Key	Key Len	Ref	Rows	Extra
SIMPLE	xf_permission_combination	const	PRIMARY	PRIMARY	4	const	1

1. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
2. XF\Db\AbstractAdapter->query() in src/XF/Db/AbstractAdapter.php at line 150
3. XF\Db\AbstractAdapter->fetchOne() in src/XF/PermissionCache.php at line 32
4. XF\PermissionCache->getGlobalPerms() in src/XF/PermissionSet.php at line 35
5. XF\PermissionSet->hasGlobalPermission() in src/XF/Entity/User.php at line 1176
6. XF\Entity\User->hasPermission() in src/XF/Entity/User.php at line 255
7. XF\Entity\User->isLinkTrusted() in src/XF/BbCode/Renderer/Html.php at line 240
8. XF\BbCode\Renderer\Html->setupRenderOptions() in src/XF/BbCode/Traverser.php at line 34
9. XF\BbCode\Traverser->renderAst() in src/XF/BbCode/Traverser.php at line 20
10. XF\BbCode\Traverser->render() in src/XF/SubContainer/BbCode.php at line 220
11. XF\SubContainer\BbCode->render() in src/XF/Template/Templater.php at line 2343
12. XF\Template\Templater->fnBbCode() in src/XF/Template/Templater.php at line 2358
13. XF\Template\Templater->fnBbCodeSnippet()
14. call_user_func_array() in src/XF/Template/Templater.php at line 1103
15. XF\Template\Templater->func() in internal_data/code_cache/templates/l1/s25/public/post_macros.php at line 451
16. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 799
17. XF\Template\Templater->callMacro() in internal_data/code_cache/templates/l1/s25/public/post_article_macros.php at line 96
18. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 799
19. XF\Template\Templater->callMacro() in internal_data/code_cache/templates/l1/s25/public/forum_view_type_article.php at line 144
20. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 799
21. XF\Template\Templater->callMacro() in internal_data/code_cache/templates/l1/s25/public/forum_view_type_article.php at line 54
22. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 962
23. XF\Template\Templater->renderExtensionInternal() in src/XF/Template/Templater.php at line 898
24. XF\Template\Templater->renderExtension() in internal_data/code_cache/templates/l1/s25/public/forum_view.php at line 641
25. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 1626
26. XF\Template\Templater->renderTemplate() in src/XF/Template/Templater.php at line 1617
27. XF\Template\Templater->renderTemplate() in src/XF/Template/Template.php at line 24
28. XF\Template\Template->render() in src/XF/Mvc/Renderer/Html.php at line 48
29. XF\Mvc\Renderer\Html->renderView() in src/XF/Mvc/Dispatcher.php at line 458
30. XF\Mvc\Dispatcher->renderView() in src/XF/Mvc/Dispatcher.php at line 440
31. XF\Mvc\Dispatcher->renderReply() in src/XF/Mvc/Dispatcher.php at line 400
32. XF\Mvc\Dispatcher->render() in src/XF/Mvc/Dispatcher.php at line 58
33. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2300
34. XF\App->run() in src/XF.php at line 488
35. XF::runApp() in index.php at line 20

 

[XF Bug Bot]

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.3).

Change log:

Fix N+1 query issues when viewing article forums.

There may be a delay before changes are rolled out to the XenForo Community.