0

I've got some third party code here that loads a larger product collection. It takes around 9 minutes to load on decent hardware. We have some 150000 SKU in the database.

Collection:

SQL is:

SELECT DISTINCT  `e`.*,
                IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`,
                IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS `visibility`,
                `price_index`.`price`,
                `price_index`.`tax_class_id`,
                `price_index`.`final_price`,
                IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`,
                `price_index`.`min_price`,
                `price_index`.`max_price`,
                `price_index`.`tier_price`,
                (SELECT MAX(rating_summary) FROM review_entity_summary AS o
                    WHERE o.entity_pk_value = e.entity_id AND o.store_id = 2) AS `rating_summary`
        FROM `catalog_product_entity` AS `e`
       INNER JOIN `catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id AND product_website.website_id = 2
       INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '97') AND `at_status_default`.`store_id` = 0
       LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '97') AND (`at_status`.`store_id` = 2)
       INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '99') AND `at_visibility_default`.`store_id` = 0
       LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '99') AND (`at_visibility`.`store_id` = 2)
       INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '2'
        WHERE (((IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1')))
          AND (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) IN(3, 2, 4))

mariadb tells me on an analyze:

id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra
1 PRIMARY product_website ref "PRIMARY,CATALOG_PRODUCT_WEBSITE_WEBSITE_ID" CATALOG_PRODUCT_WEBSITE_WEBSITE_ID 2 const 82150 50902.00 100 100 Using index; Using temporary
1 PRIMARY e eq_ref PRIMARY PRIMARY 4 magento2.product_website.product_id 1 1.00 100 100 ""
1 PRIMARY at_status eq_ref "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE,CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID" CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID 8 "magento2.product_website.product_id,const,const" 1 0.00 0 100 ""
1 PRIMARY at_visibility ref "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE,CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID" CATALOG_PRODUCT_ENTITY_INT_STORE_ID 2 const 1 0.00 100 100
1 PRIMARY at_status_default ref "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE,CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID" CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID 4 magento2.product_website.product_id 9 9.11 11.14 8.83 Using where
1 PRIMARY at_visibility_default ref "CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID,CATALOG_PRODUCT_ENTITY_INT_STORE_ID,CATALOG_PRODUCT_ENTITY_INT_ATTRIBUTE_ID_STORE_ID_VALUE,CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID" CATALOG_PRODUCT_ENTITY_INT_ENTITY_ID 4 magento2.product_website.product_id 9 9.24 11.18 10.82 Using where
1 PRIMARY price_index ref "PRIMARY,CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE" CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE 6 "const,const" 91136 1.00 100 100 Using index condition
2 DEPENDENT SUBQUERY o ref "REVIEW_ENTITY_SUMMARY_ENTITY_PK_VALUE_STORE_ID_ENTITY_TYPE,REVIEW_ENTITY_SUMMARY_STORE_ID" REVIEW_ENTITY_SUMMARY_ENTITY_PK_VALUE_STORE_ID_ENTITY_TYPE 10 "magento2.e.entity_id,const" 1 0.00 100 100 Using index condition
2
  • Just to be clear, what is your question?
    – gnicko
    Feb 26 at 15:53
  • I want to understand why that is so slow, and what do do about that. Grabbing that data set from the database should not take minutes. I can do a full DB import faster than that- Feb 27 at 16:02

1 Answer 1

0

I think this Select could be slow because, your sub-select in your select can not be optimized automaticaly and will run over 150000 times. This sub-select is not heavy but runs 150000 with out a re-use.

If you put in the sub-select in the from part, it will run just once. This could look like this:

SELECT DISTINCT  `e`.*,
             IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`,
             IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS `visibility`,
             `price_index`.`price`,
             `price_index`.`tax_class_id`,
             `price_index`.`final_price`,
             IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`,
             `price_index`.`min_price`,
             `price_index`.`max_price`,
             `price_index`.`tier_price`,
             rating_summary.mx as rating_summary
FROM `catalog_product_entity` AS `e`
LEFT JOIN
 (SELECT e.entity_id, MAX(rating_summary) as mx
  FROM review_entity_summary AS o,
       catalog_product_entity as e
  WHERE o.entity_pk_value = e.entity_id AND o.store_id = 2
  GROUP BY e.entity_id) AS `rating_summary` ON e.entity_id = rating_summary.entity_id
     INNER JOIN `catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id AND product_website.website_id = 2
     INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '97') AND `at_status_default`.`store_id` = 0
     LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '97') AND (`at_status`.`store_id` = 2)
     INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '99') AND `at_visibility_default`.`store_id` = 0
     LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '99') AND (`at_visibility`.`store_id` = 2)
     INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0 AND price_index.website_id = '2' 
WHERE (((IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1')))
AND (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) IN(3, 2, 4));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.