Incorrect index table used in getProductsCountQuery() (fallback masks issue) #40320
Description
Preconditions and environment
The method getProductsCountQuery() in Magento\Catalog\Model\ResourceModel\Category\Collection still queries the legacy index table catalog_category_product_index, even though Magento now uses store-segmented index tables, such as:
catalog_category_product_index_store1
catalog_category_product_index_store2
Problematic code:
private function getProductsCountQuery(array $categoryIds, $addVisibilityFilter = true): Select
{
// Legacy table, not used in store-segmented indexing
$categoryTable = $this->_resource->getTableName('catalog_category_product_index');
...
}
In practice, this does not break product counting, because the logic falls back to:
getProductsCountFromCategoryTable()
or getCountFromCategoryTableBulk()
These fallbacks use correct tables and correctly filter by website/store.
However:
- The first query remains technically incorrect
- Creates unnecessary load
- Is misleading for developers reading/debugging the code
- Is inconsistent with the fallback logic
- Uses a misleading $addVisibilityFilter boolean
This results in confusion, redundant work, and inconsistent query paths.
Steps to reproduce
1 - Load a category collection with product counts enabled:
$collection->setLoadProductCount(true)->load();
2 - Observe SQL generated by getProductsCountQuery().
3 - Notice that the first query hits:
catalog_category_product_index
which is not a real per-store index source.
4 - Observe that fallback logic corrects the result, so final counts happen to be accurate.
Expected result
The product count query should:
- Immediately use the proper store-segmented index tables
- Or use the fallback logic directly
- Or select index table based on $storeId
- Avoid triggering a first invalid/legacy query
- Expose meaningful parameter naming ($applyVisibilityFilter)
Actual result
Inaccurate first query
SELECT category_id, COUNT(product_id)
FROM catalog_category_product_index
==> Never returns accurate data on modern Magento installations.
getProductsCountFromCategoryTable() provides the correct results, entirely bypassing the initial query.
Result
- No functional bug, but inconsistent code
- Additional SQL overhead
- Confusing API behavior
- Two separate code paths producing counts differently
Additional information
- The first SQL query is guaranteed to be wrong (wrong table).
- Fallback logic corrects it but hides the underlying problem.
- Developers reading code or debugging DB logs will see misleading behavior.
- It contradicts Magento’s own indexation model.
- It harms the code clarity.
- It may degrade performance on stores with large categories.
Release note
Corrected category product count logic to use store-segmented index tables and removed legacy table usage.
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”.