1

When navigating to backend order listing page, the site is extremely slow and takes about more than 30mins before displaying the order info.

Looking at show full processlist on mysql, it looks like this query is causing the slow down:

SELECT `main_table`.*, `temp_sales_order_items`.*, `order_address_shipping_tbl`.`company` AS `shipping_company`, `order_address_shipping_tbl`.`street` AS `shipping_street`, `order_address_shipping_tbl`.`city` AS `shipping_city`, `order_address_shipping_tbl`.`region` AS `shipping_region`, `order_address_shipping_tbl`.`country_id` AS `shipping_country_id`, `order_address_shipping_tbl`.`postcode` AS `shipping_postcode`, `order_address_shipping_tbl`.`telephone` AS `shipping_telephone`, `temp_shipment_grid`.*, `temp_shipment_track`.*, `order_payment_tbl`.`method`, `temp_sales_order_comments`.*, `temp_sales_order`.* FROM `sales_flat_order_grid` AS `main_table`
 LEFT JOIN (SELECT `sales_flat_order_item`.`order_id`, `sales_flat_order_item`.`parent_item_id`, GROUP_CONCAT(`name` SEPARATOR '\n') AS `product_names`, GROUP_CONCAT(`sku` SEPARATOR '\n') AS `skus`, GROUP_CONCAT(`product_id` SEPARATOR '\n') AS `product_ids`, GROUP_CONCAT(`product_options` SEPARATOR '^') AS `product_options`, SUM(`qty_refunded`) AS `total_qty_refunded`, SUM(`qty_ordered`) AS `total_qty_ordered_aggregated`, SUM(`qty_canceled`) AS `total_qty_canceled`, SUM(`qty_invoiced`) AS `total_qty_invoiced` FROM `sales_flat_order_item` GROUP BY `order_id`) AS `temp_sales_order_items` ON `main_table`.`entity_id` = `temp_sales_order_items`.`order_id` AND `temp_sales_order_items`.`parent_item_id` IS NULL
 LEFT JOIN `sales_flat_order_address` AS `order_address_shipping_tbl` ON main_table.entity_id = order_address_shipping_tbl.parent_id AND order_address_shipping_tbl.`address_type` = "shipping"
 LEFT JOIN (SELECT (IF(IFNULL(`entity_id`, 0)>0, 1, 0)) AS `shipped`, SUM(`total_qty`) AS `total_qty_shipped`, `sales_flat_shipment_grid`.`order_id` AS `shipment_order_id` FROM `sales_flat_shipment_grid` GROUP BY `shipment_order_id`) AS `temp_shipment_grid` ON `main_table`.`entity_id` = `temp_shipment_grid`.`shipment_order_id`
 LEFT JOIN (SELECT GROUP_CONCAT(`track_number` SEPARATOR '\n') AS `tracking_number`, `sales_flat_shipment_track`.`parent_id` AS `tracking_parent_id`, `sales_flat_shipment_track`.`order_id` AS `tracking_order_id` FROM `sales_flat_shipment_track` GROUP BY `tracking_order_id`) AS `temp_shipment_track` ON `main_table`.`entity_id` = `temp_shipment_track`.`tracking_order_id`
 LEFT JOIN `sales_flat_order_payment` AS `order_payment_tbl` ON `main_table`.`entity_id` = `order_payment_tbl`.`parent_id`
 LEFT JOIN (SELECT GROUP_CONCAT(`comment` SEPARATOR '\n') AS `order_comment`, `sales_flat_order_status_history`.`parent_id` AS `comment_parent_id` FROM `sales_flat_order_status_history` GROUP BY `comment_parent_id`) AS `temp_sales_order_comments` ON `main_table`.`entity_id` = `temp_sales_order_comments`.`comment_parent_id`
 LEFT JOIN (SELECT `sales_flat_order`.`subtotal`, `sales_flat_order`.`base_subtotal`, `sales_flat_order`.`shipping_amount`, `sales_flat_order`.`base_shipping_amount`, `sales_flat_order`.`entity_id` AS `temp_entity_id` FROM `sales_flat_order` GROUP BY `entity_id`) AS `temp_sales_order` ON `main_table`.`entity_id` = `temp_sales_order`.`temp_entity_id` WHERE (main_table.order_group_id = '0') ORDER BY main_table.created_at DESC LIMIT 20

I have installed Extended Orders extension (http://www.mageworx.com/extended-orders-magento-extension.html). Other than that, I haven't done any other customization.

Looks like an inefficient query or missing indexes but I can't debug it myself. Anyhelp is much appreciated.

Explain results as below:

+----+-------------+---------------------------------+-------+----------------------------------------+-----------------------------------------------+---------+---------------------------------+-------+----------------------------------------------+
| id | select_type | table                           | type  | possible_keys                          | key                                           | key_len | ref                             | rows  | Extra                                        |
+----+-------------+---------------------------------+-------+----------------------------------------+-----------------------------------------------+---------+---------------------------------+-------+----------------------------------------------+
|  1 | PRIMARY     | main_table                      | ALL   | NULL                                   | NULL                                          | NULL    | NULL                            | 15552 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | <derived2>                      | ALL   | NULL                                   | NULL                                          | NULL    | NULL                            | 17923 |                                              |
|  1 | PRIMARY     | order_address_shipping_tbl      | ref   | IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID | IDX_SALES_FLAT_ORDER_ADDRESS_PARENT_ID        | 5       | magento_19.main_table.entity_id |     1 |                                              |
|  1 | PRIMARY     | <derived3>                      | ALL   | NULL                                   | NULL                                          | NULL    | NULL                            |  4849 |                                              |
|  1 | PRIMARY     | <derived4>                      | ALL   | NULL                                   | NULL                                          | NULL    | NULL                            |  4830 |                                              |
|  1 | PRIMARY     | order_payment_tbl               | ref   | IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID | IDX_SALES_FLAT_ORDER_PAYMENT_PARENT_ID        | 4       | magento_19.main_table.entity_id |     1 |                                              |
|  1 | PRIMARY     | <derived5>                      | ALL   | NULL                                   | NULL                                          | NULL    | NULL                            | 17923 |                                              |
|  1 | PRIMARY     | <derived6>                      | ALL   | NULL                                   | NULL                                          | NULL    | NULL                            | 17918 |                                              |
|  6 | DERIVED     | sales_flat_order                | index | NULL                                   | PRIMARY                                       | 4       | NULL                            | 18631 |                                              |
|  5 | DERIVED     | sales_flat_order_status_history | index | NULL                                   | IDX_SALES_FLAT_ORDER_STATUS_HISTORY_PARENT_ID | 4       | NULL                            | 60711 |                                              |
|  4 | DERIVED     | sales_flat_shipment_track       | index | NULL                                   | IDX_SALES_FLAT_SHIPMENT_TRACK_ORDER_ID        | 4       | NULL                            |  4885 |                                              |
|  3 | DERIVED     | sales_flat_shipment_grid        | index | NULL                                   | IDX_SALES_FLAT_SHIPMENT_GRID_ORDER_ID         | 4       | NULL                            |  4697 |                                              |
|  2 | DERIVED     | sales_flat_order_item           | index | NULL                                   | IDX_SALES_FLAT_ORDER_ITEM_ORDER_ID            | 4       | NULL                            | 73325 |                                              |
+----+-------------+---------------------------------+-------+----------------------------------------+-----------------------------------------------+---------+---------------------------------+-------+----------------------------------------------+
13 rows in set, 9458 warnings (0.50 sec)
  • You mention the extended orders extention. Does the issue remain if you disable that extension? – ProxiBlue Aug 7 '16 at 16:11
  • Did you found a way to reduce the loadtime? I have the same problem with my orders grid (take ages to load...) – DarkCowboy Jun 4 '18 at 15:05
2

Log all of you db queries by setting $_debug = true; in lib\Varien\Db\Adapter\Pdo\Mysql.php and this will output all sql query to var/debug/pdo_mysql.log.

You will get idea about how many queries are running and which one taking how much time.
Do not enable this to production environment.

Hope this will help you.

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.