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)
