Magento – Mysql ‘Copying to tmp table’ very slow

ee-1.14MySQLperformance

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

Best Answer

As said, this join can be very bad on large order item collections, cause it can't reference the catalog_category_entity table. Same goes for quote_item to CCE. So we have a join in the middle that can't use an indexed approach to it's outer parts. In worst case, MySQL tries to sort it over and over to get optimal alignment of CCE.

I'd to it like this:

Join the quotes with quote_item and put the is_active on the join condition and keep the GROUP BY.
Fetch all quote_item.product_id into an array
Fetch price info using IN($quote_item_product_ids)
Fetch times sold till now using order_items.product_id IN($quote_item_product_ids)

And/or create an index table linking sold quantities to product entities. A foreign key there can work, cause in the index table we can make the item_id columns nullable, what can't be done with a primary key.