I noticed that when I access Admin > Reports > Shopping Cart > Products in cart report in Magento EE 1.14 it gets times out. In mysql it gets stuck in 'Copying to tmp table' State for like 30 minutes!
Below is the query:
Command: Query
Time: 1650
State: Copying to tmp table
Info: SELECT STRAIGHT_JOIN `product_name`.`value` AS `name`, product_price.value * main_table.base_to_global_rate AS `price`, `e`.*, COUNT(quote_items.item_id) AS `carts`, `order_items`.`orders` FROM `sales_flat_quote` AS `main_table`
INNER JOIN `sales_flat_quote_item` AS `quote_items` ON quote_items.quote_id = main_table.entity_id
INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = quote_items.product_id
INNER JOIN `catalog_product_entity_varchar` AS `product_name` ON product_name.entity_id = e.entity_id AND product_name.attribute_id = 71
INNER JOIN `catalog_product_entity_decimal` AS `product_price` ON product_price.entity_id = e.entity_id AND product_price.attribute_id = 75
LEFT JOIN (SELECT COUNT(1) AS `orders`, `oi`.`product_id` FROM `sales_flat_order_item` AS `oi` GROUP BY `oi`.`product_id`) AS `order_items` ON order_items.product_id = e.entity_id WHERE (main_table.is_active = 1) GROUP BY `quote_items`.`product_id` LIMIT 20
Rows_sent: 0
Rows_examined: 551620
Rows_read: 551620
Query EXPLAIN EXTENDED:
mysql> explain extended SELECT STRAIGHT_JOIN `product_name`.`value` AS `name`, product_price.value * main_table.base_to_global_rate AS `price`, `e`.*, COUNT(quote_items.item_id) AS `carts`, `order_items`.`orders` FROM `sales_flat_quote` AS `main_table`
-> INNER JOIN `sales_flat_quote_item` AS `quote_items` ON quote_items.quote_id = main_table.entity_id
-> INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = quote_items.product_id
-> INNER JOIN `catalog_product_entity_varchar` AS `product_name` ON product_name.entity_id = e.entity_id AND product_name.attribute_id = 71
-> INNER JOIN `catalog_product_entity_decimal` AS `product_price` ON product_price.entity_id = e.entity_id AND product_price.attribute_id = 75
-> LEFT JOIN (SELECT COUNT(1) AS `orders`, `oi`.`product_id` FROM `sales_flat_order_item` AS `oi` GROUP BY `oi`.`product_id`) AS `order_items` ON order_items.product_id = e.entity_id WHERE (main_table.is_active = 1) GROUP BY `quote_items`.`product_id` LIMIT 20;
+----+-------------+---------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+-----------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+-----------------------------------+--------+----------+----------------------------------------------+
| 1 | PRIMARY | main_table | ALL | PRIMARY | NULL | NULL | NULL | 349938 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | quote_items | ref | IDX_SALES_FLAT_QUOTE_ITEM_PRODUCT_ID,IDX_SALES_FLAT_QUOTE_ITEM_QUOTE_ID | IDX_SALES_FLAT_QUOTE_ITEM_QUOTE_ID | 4 | prod.main_table.entity_id | 2 | 100.00 | |
| 1 | PRIMARY | e | eq_ref | PRIMARY | PRIMARY | 4 | prod.quote_items.product_id | 1 | 100.00 | |
| 1 | PRIMARY | product_name | ref | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID | 6 | prod.e.entity_id,const | 1 | 100.00 | Using where |
| 1 | PRIMARY | product_price | ref | UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID,IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ENTITY_ID,IDX_CATALOG_PRODUCT_ENTITY_DECIMAL_ATTRIBUTE_ID | UNQ_CAT_PRD_ENTT_DEC_ENTT_ID_ATTR_ID_STORE_ID | 6 | prod.e.entity_id,const | 1 | 100.00 | Using where |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 7512 | 100.00 | |
| 2 | DERIVED | oi | ALL | NULL | NULL | NULL | NULL | 539047 | 100.00 | Using temporary; Using filesort |
+----+-------------+---------------+--------+---------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------+---------+-----------------------------------+--------+----------+----------------------------------------------+
7 rows in set, 1 warning (0.58 sec)
We have tried allowing temporary table creation in memory instead of disk, without any luck.
Anyone else experiencing similar problem? Any idea how to resolve this?
Thanks
product_price.value * main_table.base_to_global_rate AS 'price',
. Could you show an EXPLAIN?INNER JOIN
sales_flat_quote_item` ASquote_items
ON quote_items.quote_id = main_table.entity_id AND main_table.is_active=1`