1

We have more then more then 2L products and 10L orders exists in multiple stores in our magento site. I was told to optimize the performance on some point of actions.

Magento ver. 1.9.1.0

  1. Admin Catelog Product Grid.
  2. Admin Order Grid.

Because we have noticed that when our sales team working on back-end mostly they are using product or order grid which is then executing some sql queries without having limit in it.

Examples:

SELECT `e`.*, `at_qty`.`qty`, `at_status`.`value` AS `status`, `at_visibility`.`value` AS `visibility`, `e`.`market_status` FROM `catalog_product_entity` AS `e` LEFT JOIN `cataloginventory_stock_item` AS `at_qty` ON (at_qty.`product_id`=e.entity_id) AND (at_qty.stock_id=1) INNER JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '84') AND (`at_status`.`store_id` = 0) INNER JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '91') AND (`at_visibility`.`store_id` = 0) INNER JOIN `catalog_product_entity` AS `table_alias2` ON e.entity_id = table_alias2.entity_id ORDER BY `e`.`entity_id` DESC

SELECT DISTINCT e.entity_id 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_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '60') AND (`at_name`.`store_id` = 0) INNER JOIN `catalog_product_entity_varchar` AS `at_custom_name` ON (`at_custom_name`.`entity_id` = `e`.`entity_id`) AND (`at_custom_name`.`attribute_id` = '60') AND (`at_custom_name`.`store_id` = 0) INNER JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '84') AND (`at_status`.`store_id` = 0) 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` = '91') AND `at_visibility_default`.`store_id` = 0 INNER JOIN `catalog_product_entity` AS `table_alias2` ON e.entity_id = table_alias2.entity_id;

I have checked this query by running in mysql workbench, they are taking long time to execute.

My senior suggest me to apply a limit of 5000 on all such queries to fix performance issue immediately.

Please help or suggest me the best way to do this.

2

Magento 1.x has a lot of bottlenecks in the existing indexation mechanisms, and on large datasets, it is very important to rewrite the indexers for specifics needs. Regular indexes are most of the times slow on edge case scenarios logic and simplifying it to your needs help to achieve much more better performance.

There are multiple approaches that can help you in achieving a better indexation speed:

  1. Ranged queries, like you already mentioned.
  2. Creating intermediate tables with indexes on important columns for index generation
  3. Reduce number of joins by creating EAV table snapshots with pre-warmed data for every store view (related to pt #2)

You can learn a bit more about caveats of database optimisation from my talk at MageTitans Italy: https://www.youtube.com/watch?v=U_XSFUJAPe4

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.