query taking too long

[ottavio]

The netsons.com provider suspended one of my accounts because of a query taking too long on their mysql server.

 

I need to know:

 

1. what is the purpose of this query and where is generated (is this a core module?);

 

2. the database contain much less than a million row, so examinig 1342308 rows to select 13 seems highly inefficient, is there anything I can do?

 

At least, I would need a way to find where this query is executed in the code.

 

Any help will be greatly appreciated!

 

The version of PrestaShop is 1.5.1.0

 

This is an example from the MySQL slow query log they sent me:

 

# Time: 131130 15:05:06

# Query_time: 15.582961 Lock_time: 0.000688 Rows_sent: 12 Rows_examined: 1342308

SET timestamp=1385820306;

SELECT cp.`id_product_attribute`, cp.`id_product`, cp.`quantity` AS cart_quantity, cp.id_shop, pl.`name`, p.`is_virtual`,

pl.`description_short`, pl.`available_now`, pl.`available_later`, p.`id_product`, product_shop.`id_category_default`, p.`id_supplier`,

p.`id_manufacturer`, product_shop.`on_sale`, product_shop.`ecotax`, product_shop.`additional_shipping_cost`, product_shop.`available_for_order`, product_shop.`price`, p.`weight`,

stock.`quantity` quantity_available, p.`width`, p.`height`, p.`depth`, stock.`out_of_stock`, product_shop.`active`, p.`date_add`,

p.`date_upd`, t.`id_tax`, tl.`name` AS tax, t.`rate`, IFNULL(stock.quantity, 0) as quantity, pl.`link_rewrite`, cl.`link_rewrite` AS category,

CONCAT(cp.`id_product`, cp.`id_product_attribute`, cp.`id_address_delivery`) AS unique_id, cp.id_address_delivery,

product_shop.`wholesale_price`, product_shop.advanced_stock_management,

cu.`id_customization`, cu.`quantity` AS customization_quantity,

product_attribute_shop.`price` AS price_attribute, product_attribute_shop.`ecotax` AS ecotax_attr,

IF (IFNULL(pa.`reference`, '') = '', p.`reference`, pa.`reference`) AS reference,

IF (IFNULL(pa.`supplier_reference`, '') = '', p.`supplier_reference`, pa.`supplier_reference`) AS supplier_reference,

(p.`weight`+ pa.`weight`) weight_attribute,

IF (IFNULL(pa.`ean13`, '') = '', p.`ean13`, pa.`ean13`) AS ean13,

IF (IFNULL(pa.`upc`, '') = '', p.`upc`, pa.`upc`) AS upc,

pai.`id_image` as pai_id_image, il.`legend` as pai_legend,

IFNULL(product_attribute_shop.`minimal_quantity`, product_shop.`minimal_quantity`) as minimal_quantity

FROM `ps_cart_product` cp

LEFT JOIN `ps_product` `p` ON p.`id_product` = cp.`id_product`

INNER JOIN `ps_product_shop` product_shop ON product_shop.id_shop=1

LEFT JOIN `ps_product_lang` `pl` ON 

p.`id_product` = pl.`id_product`

AND pl.`id_lang` = 6 AND pl.id_shop = 1 

LEFT JOIN `ps_tax_rule` `tr` ON 

product_shop.`id_tax_rules_group` = tr.`id_tax_rules_group`

AND tr.`id_country` = 10

AND tr.`id_state` = 0

AND tr.`zipcode_from` = 0

LEFT JOIN `ps_tax` `t` ON t.`id_tax` = tr.`id_tax`

LEFT JOIN `ps_tax_lang` `tl` ON 

t.`id_tax` = tl.`id_tax`

AND tl.`id_lang` = 6

LEFT JOIN `ps_category_lang` `cl` ON 

product_shop.`id_category_default` = cl.`id_category`

AND cl.`id_lang` = 6 AND cl.id_shop = 1 

LEFT 

JOIN ps_stock_available stock

ON (stock.id_product = cp.id_product AND stock.id_product_attribute = IFNULL(`cp`.id_product_attribute, 0) AND stock.id_shop = 1 )

LEFT JOIN `ps_customization` `cu` ON p.`id_product` = cu.`id_product` AND cp.`id_product_attribute` = cu.id_product_attribute AND cu.id_cart=284

LEFT JOIN `ps_product_attribute` `pa` ON pa.`id_product_attribute` = cp.`id_product_attribute`

LEFT JOIN `ps_product_attribute_shop` `product_attribute_shop` ON product_attribute_shop.id_shop=1

LEFT JOIN `ps_product_attribute_image` `pai` ON pai.`id_product_attribute` = pa.`id_product_attribute`

LEFT JOIN `ps_image_lang` `il` ON il.id_image = pai.id_image AND il.id_lang = 6

WHERE (cp.`id_cart` = 284) AND (p.`id_product` IS NOT NULL)

GROUP BY unique_id

ORDER BY p.id_product, cp.id_product_attribute, cp.date_add ASC;

 

 

 

 

[NemoPS]

How many products do you have in your store?

I assume you use many combinations, correct?

[ottavio]

We have near 600 products, and we use combinations but in a minority of products.

 

These are the numbers I gather from a backup, which I think are relevant to the query:

 

mysql> select count(*) from ps_cart_product;

400

 

mysql> select count(*) from ps_product;

576

 

mysql> select count(*) from ps_product_shop;

577

 

mysql> select count(*) from ps_product_lang;

3462

Edited December 2, 2013 by ottavio (see edit history)

[NemoPS]

I see.

Is there a chance you can upgrade to 1.5.5 perhaps? Or even the latest 1.5.6.1

[ottavio]

This is something we will have to do but not in the near future, because we have heavily customized many of the modules we use

[ottavio]

I found the query is generated in the method getProducts of classes/Cart.php

 

When combinations are active 4 leftJoins are added to the query.

 

I checked the code of the same method on the last version of PrestaShop on github:

 

https://github.com/PrestaShop/PrestaShop/blob/bootstrap/classes/Cart.php

 

and there is a difference on the second leftJoin:

 

[OLD]  $sql->leftJoin('product_attribute_shop', 'product_attribute_shop', 'product_attribute_shop.id_shop='.$id_shop);

[NEW] $sql->leftJoin('product_attribute_shop', 'product_attribute_shop', '(product_attribute_shop.id_shop=cp.id_shop AND product_attribute_shop.id_product_attribute = pa.id_product_attribute)');

 

Changing this condition and executing the query on my computer, the execution time passed from 8.25s to 0.2s