addCountToCategories method produce very slow queries

[davidelitrico]

Hi guys,

Have you ever expericed some very slow queries which starts from this method:

addCountToCategories

vendor/magento/module-catalog/Model/ResourceModel/Product/Collection.php(1302) ?

the query I'm referring to is like this:

SELECT `count_table`.`category_id`, COUNT(DISTINCT count_table.product_id) AS `product_count` FROM `catalog_product_flat_14` AS `e`
 INNER JOIN `catalog_category_product_index_store14` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=14 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=2146
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '8' AND price_index.customer_group_id = 0
 INNER JOIN `search_tmp_5e6bed86dbfce4_28985117` AS `search_result` ON e.entity_id = search_result.entity_id
 LEFT JOIN `review_entity_summary` AS `review_summary` ON e.entity_id = review_summary.entity_pk_value AND review_summary.store_id = 14 AND review_summary.entity_type = (SELECT `review_entity`.`entity_id` FROM `review_entity` WHERE (entity_code = 'product'))
 INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id
 INNER JOIN `catalog_category_product_index_store14` AS `count_table` ON count_table.product_id = e.entity_id WHERE (count_table.store_id = 14) AND (count_table.category_id IN ('1641', '2017', '1830', '1924', '2326', '1938', '1816', '2004', '1823')) GROUP BY `count_table`.`category_id`

What have you done to improve the performance?
I’m using v2.3.3

Thank you

[suman_kar]

addCountToCategories() mostly used by the layered navigation in the Product List page to show the product count under for each subcategory under a category.

If you what product count for a specific category you can use  getProductCount()

On category object just execute getProductCount()

Reference can be found in:vendor/magento/module-catalog/Model/Layer/Filter/Category.php

if ($category->getIsActive()) {
    foreach ($categories as $category) {
        if ($category->getIsActive() && $category->getProductCount()) {
            $this->itemDataBuilder->addItemData(
                $this->_escaper->escapeHtml($category->getName()),
                $category->getId(),
                $category->getProductCount()
            );
        }
    }
}

Magento Core Class : Magento\Catalog\Model\Category

/**
 * Retrieve count products of category
 *
 * @return int
 */
public function getProductCount()
{
    if (!$this->hasData(self::KEY_PRODUCT_COUNT)) {
        $count = $this->_getResource()->getProductCount($this);
        $this->setData(self::KEY_PRODUCT_COUNT, $count);
    }

    return $this->getData(self::KEY_PRODUCT_COUNT);
}

[Suman Kar · Accepted solution]

addCountToCategories() mostly used by the layered navigation in the Product List page to show the product count under for each subcategory under a category.

If you what product count for a specific category you can use  getProductCount()

On category object just execute getProductCount()

Reference can be found in:vendor/magento/module-catalog/Model/Layer/Filter/Category.php

if ($category->getIsActive()) {
    foreach ($categories as $category) {
        if ($category->getIsActive() && $category->getProductCount()) {
            $this->itemDataBuilder->addItemData(
                $this->_escaper->escapeHtml($category->getName()),
                $category->getId(),
                $category->getProductCount()
            );
        }
    }
}

Magento Core Class : Magento\Catalog\Model\Category

/**
 * Retrieve count products of category
 *
 * @return int
 */
public function getProductCount()
{
    if (!$this->hasData(self::KEY_PRODUCT_COUNT)) {
        $count = $this->_getResource()->getProductCount($this);
        $this->setData(self::KEY_PRODUCT_COUNT, $count);
    }

    return $this->getData(self::KEY_PRODUCT_COUNT);
}