1

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

13
  • Is this accurate? product_price.value * main_table.base_to_global_rate AS 'price', . Could you show an EXPLAIN?
    – user4351
    Jun 7, 2015 at 17:42
  • @Melvyn updated
    – Kalpesh
    Jun 7, 2015 at 17:48
  • Try removing the WHERE and adding it to the first join condition. What does explain say then? So: INNER JOIN sales_flat_quote_item` AS quote_items ON quote_items.quote_id = main_table.entity_id AND main_table.is_active=1`
    – user4351
    Jun 7, 2015 at 17:51
  • did not helped, it's showing same explain as original
    – Kalpesh
    Jun 7, 2015 at 18:04
  • 1
    Ok thanks. I had around 190000 records.
    – Kalpesh
    Jul 6, 2015 at 18:13

1 Answer 1

1

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:

  1. Join the quotes with quote_item and put the is_active on the join condition and keep the GROUP BY.
  2. Fetch all quote_item.product_id into an array
  3. Fetch price info using IN($quote_item_product_ids)
  4. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.