[BUG] getProductCount() has two implementations. One uses an unnecessary GROUP BY clause, leading to: Using temporary; Using filesort

[loekvangool]

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

There are two implementations that should probably be equal:

https://github.com/OpenMage/magento-lts/blob/v20.12/app/code/core/Mage/Catalog/Model/Resource/Category/Flat.php#L1205

SELECT
    COUNT(
        catalog_category_product.product_id
    )
FROM
    `catalog_category_product`
WHERE
    (
        catalog_category_product.category_id = '845'
    )
GROUP BY
    `catalog_category_product`.`category_id`

https://github.com/OpenMage/magento-lts/blob/v20.12/app/code/core/Mage/Catalog/Model/Resource/Category.php#L517

SELECT
    COUNT(main_table.product_id)
FROM
    `catalog_category_product` AS `main_table`
WHERE
    (main_table.category_id = 845)

The Flat version uses an additional GROUP BY clause (on the COUNT of a set of rows that is already unique), leading to much more difficult optimization in MySQL:

Using where; Using index; Using temporary; Using filesort vs. Using index if the GROUP BY clause is not used.

Expected Behavior

The GROUP BY clause should be deleted without any impact other than less temporary tables.

Steps To Reproduce

Open a category with Catalog Flat Tables enabled and disabled, while recording the queries.

Environment

Anything else?

No response