Magento Slow Category Count Query

We have recently deployed some changes to our Magento store, which has triggered the following SQL to be run:

SELECT `count_table`.`category_id`, COUNT(DISTINCT count_table.product_id) AS `product_count`

FROM `catalog_product_flat_1` AS `e`

INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND 1

INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0

INNER JOIN `catalog_product_index_eav` AS `brand_idx` ON brand_idx.entity_id = e.entity_id AND brand_idx.attribute_id = '135' AND brand_idx.store_id = 1 AND brand_idx.value IN('967')

INNER JOIN `catalog_product_flat_1` ON catalog_product_flat_1.entity_id=e.entity_id

INNER JOIN `catalog_product_flat_1` AS `catalog_product_flat_1_2` ON catalog_product_flat_1.entity_id=e.entity_id

INNER JOIN `catalog_category_product_index` AS `count_table` ON count_table.product_id = e.entity_id

WHERE (e.status = 1) AND (catalog_product_flat_1.brand = '967') AND (catalog_product_flat_1.brand = '967') AND (count_table.store_id = 1) AND (count_table.category_id IN ('335', '334', '332', '339', '337', '943'))

GROUP BY `count_table`.`category_id`;


This SQL takes a couple of seconds to run and if a number of users hit the same page at once, the server will eventually grind to a halt as the queries back up.

Running an EXPLAIN provides this:

EXPLAIN

1 SIMPLE brand_idx ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE 4 const 17 Using where; Using index; Using temporary; Using filesort
1 SIMPLE count_table ref PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY 6 db.brand_idx.entity_id,const 1 Using where; Using index
1 SIMPLE e eq_ref PRIMARY,IDX_CATALOG_PRODUCT_FLAT_1_STATUS PRIMARY 4 db.brand_idx.entity_id 1 Using where
1 SIMPLE cat_index ref IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY 6 db.brand_idx.entity_id,const 1 Using where; Using index
1 SIMPLE catalog_product_flat_1 eq_ref PRIMARY PRIMARY 4 db.brand_idx.entity_id 1 Using where
1 SIMPLE price_index eq_ref PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE PRIMARY 8 db.brand_idx.entity_id,const,const 1 Using index
1 SIMPLE catalog_product_flat_1_2 index NULL IDX_CATALOG_PRODUCT_FLAT_1_ATTRIBUTE_SET_ID 2 NULL 21529 Using index; Using join buffer (flat, BNL join)


Which, to me, suggests that there is a missing index somewhere, based on the brand_idx table using Using temporary; Using filesort. But is that true?

brand_idx

Using temporary; Using filesort

If it is, how do I identify the missing index and, secondly, how would I apply this index in Magento?

I know it's also related to selecting COUNT(DISTINCT count_table.product_id) and grouping by count_table.category_id as removing either of these parts results in a quicker query (just without the information that's expected!).

COUNT(DISTINCT count_table.product_id)

count_table

category_id

group by

1 Answer
1

This issue is a result of the two inner join's that reference the same table, catalog_product_flat_1, but the join condition on the second instance of the join references the table from the first:

inner join

catalog_product_flat_1

INNER JOIN `catalog_product_flat_1` ON catalog_product_flat_1.entity_id=e.entity_id

INNER JOIN `catalog_product_flat_1` AS `catalog_product_flat_1_2` ON **catalog_product_flat_1**.entity_id=e.entity_id


Although the question references Magento, this is actually to do with Zend_Db and the way you define the joins.

Given a Zend_Db_Select object, you can create a join like so:

$select->joinInner('catalog_product_flat_1', 'catalog_product_flat_1.entity_id = e.entity_id');


This is ok the first time you do it, but if you were to do it a second time, the joinInner function will intelligently identify a second join on the catalog_product_flat_1 table and call it catalog_product_flat_1_2, but the issue occurs because it does not identify the incorrect table alias in the join condition.

joinInner

catalog_product_flat_1

catalog_product_flat_1_2

The way to resolve this is to explicitly set the table alias name by providing a name correlation, like so:

$select->joinInner(array('unique_table_alias' => 'catalog_product_flat_1'), 'unique_table_alias.entity_id = e.entity_id');


With the second join referencing the correct table alias, the query time was reduced from 3s to 20ms.

3s

20ms

The fact that Magento is adding the inner joins twice is another matter, but once configured correctly, doesn't affect performance.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.