Preconditions and environment

• Magento 2.4.8
• Setup a catalog with:
  • 7 root categories
  • 1500 categories spread in these 7 categories
  • 175k products associated randomly to several categories (around 226k link between categories and products)

Steps to reproduce

• Go in backofiice
• Click on Catalog -> Categories
• Take a coffe, a shower, look at a TVShow and come back

Expected result

Category page appears quickly

Actual result

Category page doesn't appear quickly AT ALL !

Additional information

This is due to this commit.

More precisely there is an awful subselect here in the new function getCountFromCategoryTableBulk leading to this kind of query

SELECT `ce`.`entity_id`,
       COUNT(DISTINCT cp.product_id) AS `product_count`
FROM   `catalog_category_entity` AS `ce`
       LEFT JOIN `catalog_category_product` AS `cp`
              ON cp.category_id IN (SELECT `ce2`.`entity_id`
                                    FROM   `catalog_category_entity` AS `ce2`
                                    WHERE  ( ce2.path LIKE CONCAT(ce.path, '/%')
                                              OR ce2.path = ce.path ))
WHERE  ( ce.entity_id IN( 3, 4, 5, 6, ..., 5484, 5487, 5489, 5490 ) )
GROUP  BY `ce`.`entity_id`; 

killing performances.

This can be easily improved using a simple join

SELECT ce.entity_id,
       COUNT(DISTINCT cp.product_id) AS product_count
FROM   catalog_category_entity ce
       LEFT JOIN catalog_category_entity ce2
              ON ce2.path LIKE CONCAT(ce.path, '/%')
                  OR ce2.path = ce.path
       LEFT JOIN catalog_category_product cp
              ON cp.category_id = ce2.entity_id
WHERE  ce.entity_id IN ( 3, 4, 5, 6, ..., 5484, 5487, 5489, 5490 )
GROUP  BY ce.entity_id; 

Release note

No response

Triage and priority

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.