Performance issue with large category selection

[DanielSiepmann]

Bug Report

Current Behavior The SQL query for selecting news is very slow if a large set of categories is selected. We have a range between 0.2 and 0.35 seconds for a single plugin. Looks a single sub query is made per category instead of a single sub query for all categories which ends up in 0.1 seconds in our example.

Expected behavior/output An faster more optimized SQL query that doesn't slow down uncached versions of the page.

Environment

• TYPO3 version(s): 11.5.20
• news version: 9.4.0
• Is your TYPO3 installation set up with Composer (Composer Mode): yes
• OS: Ubuntu 20.04 LTS

Possible Solution Optimize Extbase Query Building within NewsRepository

[DanielSiepmann]

I'm on this issue and try to come up with a PR, but can't promise anything yet.

[A-Kalkhoff]

Issue is present in news version 8.6.0, 9.1.0 and 10.0.3 as well. In Non-Composer mode installation

[DanielSiepmann]

I could modify the query to end up with a simple IN(category uids) which is way faster. But that won't work with AND. And I don't have any solution for "and" so far …

[georgringer]

So maybe the query could be changed for the or query but use existing for and combination? I guess the or is used more often

[A-Kalkhoff]

The OR is surely used more often, but while the OR might be able to be optimized it is functional without causing problems. The AND however is causing performance problems or worse depending on the amount of categories.

I'm unsure whether this problem can be solved with only SQL. Perhaps querying the sys_category_record_mm by using something along the lines of SELECT GROUP_CONCAT(uid_foreign) FROM sys_category_record_mm WHERE FIND_IN_SET(uid_local, "LIST_UIDs") AND ( ( 'sys_category_record_mm'.'fieldname' = 'categories')     AND (       'sys_category_record_mm'.'tablenames' = 'tx_news_domain_model_news'     ) )

Then tidy up duplicates from that list and pass that for the IN-query from the faulty query in case of AND?

[DanielSiepmann]

I also played with two queries, one fetching all news uids from record_mm and one news query adding them. But that made the news query way slower as MySQL no longer used any index. I'm not a pro in that area and don't know why that happened … That would be one way to solve the "and" and "or" in one way.

[DanielSiepmann]

I'm not able to build the query via Extbase … Joins don't speed things up. so using contains('category.uid is no option. Using a single subquery instead of multiple improves performance.

AND (tx_news_domain_model_news.uid IN ( SELECT uid_foreign FROM `sys_category_record_mm` WHERE `sys_category_record_mm`.`fieldname` = 'categories' AND `sys_category_record_mm`.`tablenames` = 'tx_news_domain_model_news' AND `sys_category_record_mm`.`uid_local` IN (1, 12, 19, 277, 278, 279, 280, 290, 297, 298, 3, 308, 309, 310, 311, 360, 379, 381, 385, 390, 4, 413, 5, 61, 79))) as constraint would help. But I don't see any way to build that using Extbase. One would need Doctrine DBAL to properly build the query by hand. But that's not compatible with the rest of the query building within EXT:news which uses Extbase query building.

[georgringer]

closing the issue as won't fix, nothing I can really do here