Slow Queries

[webmojito]

I have a problema with my DB.

 

My hosting tell me that there is a problem with slow queries. 

 

Here the output: 

 

Can you help me? 

 

=== TOP 10 of 1078 (total) Slow Queries for the past 24 hours ==========
1. Executed 15h 50m 26s ago for 46.655305 sec on Database --> xxxx
Date: 2016-03-05 19:54:25 Query_time: 46.655305 Rows_examined: 4292757: Rows_sent 6 Lock_time: 0.000478
# Schema: noxrider_db1 Last_errno: 0 Killed: 0 SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name, product_shop.`date_add` > "2007-09-09" as new, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute FROM `ps_product` p INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_product_lang` `pl` ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = 5 AND pl.id_shop = 1 LEFT JOIN `ps_image` `i` ON i.`id_product` = p.`id_product` LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `ps_image_lang` `il` ON i.`id_image` = il.`id_image` AND il.`id_lang` = 5 LEFT JOIN `ps_manufacturer` `m` ON m.`id_manufacturer` = p.`id_manufacturer` JOIN ps_category_product cp ON (cp.id_product = p.id_product) JOIN ps_category_group cg ON (cg.id_category = cp.id_category) LEFT OUTER JOIN `ps_product_attribute` pa ON p.`id_product` = pa.`id_product` LEFT JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.default_on = 1) LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 AND stock.id_shop_group = 0 ) WHERE (product_shop.`active` = 1) AND (product_shop.`visibility` IN ("both", "catalog")) AND (product_shop.`date_add` > "2007-09-09") AND (cg.`id_group` = 1) GROUP BY product_shop.id_product ORDER BY p.`date_add` DESC LIMIT 2184, 6; 
------------------------------------------------------------------------------------------------------- 
2. Executed 16h 6m 24s ago for 45.629909 sec on Database --> xxxx 
Date: 2016-03-05 19:38:27 Query_time: 45.629909 Rows_examined: 3117331: Rows_sent 6 Lock_time: 0.011489 
# Schema: noxrider_db1 Last_errno: 0 Killed: 0 SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer, MAX(image_shop.`id_image`) id_image, il.`legend`, ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 3100 DAY)) > 0 AS new, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity FROM `ps_product_sale` ps LEFT JOIN `ps_product` p ON ps.`id_product` = p.`id_product` LEFT JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product`) LEFT JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1) LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 AND stock.id_shop_group = 0 ) LEFT JOIN `ps_product_lang` pl ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = 5 AND pl.id_shop = 1 LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 5) LEFT JOIN `ps_manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) 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 LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`) JOIN `ps_category_product` cp ON (cp.`id_product` = p.`id_product`) JOIN `ps_category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` = 1) WHERE product_shop.`active` = 1 AND p.`visibility` != 'none' GROUP BY product_shop.id_product ORDER BY `sales` DESC LIMIT 42, 6; 
------------------------------------------------------------------------------------------------------- 
3. Executed 15h 50m 33s ago for 44.122057 sec on Database --> xxxx 
Date: 2016-03-05 19:54:18 Query_time: 44.122057 Rows_examined: 4290572: Rows_sent 5 Lock_time: 0.000459 
# Schema: noxrider_db1 Last_errno: 0 Killed: 0 SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name, product_shop.`date_add` > "2007-09-09" as new, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute FROM `ps_product` p INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_product_lang` `pl` ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = 5 AND pl.id_shop = 1 LEFT JOIN `ps_image` `i` ON i.`id_product` = p.`id_product` LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `ps_image_lang` `il` ON i.`id_image` = il.`id_image` AND il.`id_lang` = 5 LEFT JOIN `ps_manufacturer` `m` ON m.`id_manufacturer` = p.`id_manufacturer` JOIN ps_category_product cp ON (cp.id_product = p.id_product) JOIN ps_category_group cg ON (cg.id_category = cp.id_category) LEFT OUTER JOIN `ps_product_attribute` pa ON p.`id_product` = pa.`id_product` LEFT JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.default_on = 1) LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 AND stock.id_shop_group = 0 ) WHERE (product_shop.`active` = 1) AND (product_shop.`visibility` IN ("both", "catalog")) AND (product_shop.`date_add` > "2007-09-09") AND (cg.`id_group` = 1) GROUP BY product_shop.id_product ORDER BY p.`date_add` DESC LIMIT 5; 
------------------------------------------------------------------------------------------------------- 
4. Executed 13h 53m 41s ago for 39.060994 sec on Database --> Unknown (NOT FOUND / DELETED) 
Date: 2016-03-05 21:51:10 Query_time: 39.060994 Rows_examined: 4291551: Rows_sent 6 Lock_time: 0.003341 
# Schema: noxrider_db1 Last_errno: 0 Killed: 0 SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, MAX(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name, product_shop.`date_add` > "2007-09-09" as new, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute FROM `ps_product` p INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_product_lang` `pl` ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = 5 AND pl.id_shop = 1 LEFT JOIN `ps_image` `i` ON i.`id_product` = p.`id_product` LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `ps_image_lang` `il` ON i.`id_image` = il.`id_image` AND il.`id_lang` = 5 LEFT JOIN `ps_manufacturer` `m` ON m.`id_manufacturer` = p.`id_manufacturer` JOIN ps_category_product cp ON (cp.id_product = p.id_product) JOIN ps_category_group cg ON (cg.id_category = cp.id_category) LEFT OUTER JOIN `ps_product_attribute` pa ON p.`id_product` = pa.`id_product` LEFT JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.default_on = 1) LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 AND stock.id_shop_group = 0 ) WHERE (product_shop.`active` = 1) AND (product_shop.`visibility` IN ("both", "catalog")) AND (product_shop.`date_add` > "2007-09-09") AND (cg.`id_group` = 1) GROUP BY product_shop.id_product ORDER BY p.`date_add` DESC LIMIT 978, 6; 
------------------------------------------------------------------------------------------------------- 
5. Executed 17h 29m 24s ago for 38.355033 sec on Database --> xxxx 
Date: 2016-03-05 18:15:27 Query_time: 38.355033 Rows_examined: 3117493: Rows_sent 6 Lock_time: 0.039544 
# Schema: noxrider_db1 Last_errno: 1160 Killed: 0 SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, pl.`available_now`, pl.`available_later`, m.`name` AS manufacturer_name, p.`id_manufacturer` as id_manufacturer, MAX(image_shop.`id_image`) id_image, il.`legend`, ps.`quantity` AS sales, t.`rate`, pl.`meta_keywords`, pl.`meta_title`, pl.`meta_description`, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL 3100 DAY)) > 0 AS new, MAX(product_attribute_shop.minimal_quantity) AS product_attribute_minimal_quantity FROM `ps_product_sale` ps LEFT JOIN `ps_product` p ON ps.`id_product` = p.`id_product` LEFT JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) LEFT JOIN `ps_product_attribute` pa ON (p.`id_product` = pa.`id_product`) LEFT JOIN ps_product_attribute_shop product_attribute_shop ON (product_attribute_shop.id_product_attribute = pa.id_product_attribute AND product_attribute_shop.id_shop = 1 AND product_attribute_shop.`default_on` = 1) LEFT JOIN ps_stock_available stock ON (stock.id_product = p.id_product AND stock.id_product_attribute = IFNULL(`product_attribute_shop`.id_product_attribute, 0) AND stock.id_shop = 1 AND stock.id_shop_group = 0 ) LEFT JOIN `ps_product_lang` pl ON p.`id_product` = pl.`id_product` AND pl.`id_lang` = 8 AND pl.id_shop = 1 LEFT JOIN `ps_image` i ON (i.`id_product` = p.`id_product`) LEFT JOIN ps_image_shop image_shop ON (image_shop.id_image = i.id_image AND image_shop.id_shop = 1 AND image_shop.cover=1) LEFT JOIN `ps_image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = 8) LEFT JOIN `ps_manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`) 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 LEFT JOIN `ps_tax` t ON (t.`id_tax` = tr.`id_tax`) JOIN `ps_category_product` cp ON (cp.`id_product` = p.`id_product`) JOIN `ps_category_group` cg ON (cp.id_category = cg.id_category AND cg.`id_group` = 1) WHERE product_shop.`active` = 1 AND p.`visibility` != 'none' GROUP BY product_shop.id_product ORDER BY `sales` DESC LIMIT 204, 6; 

 

 

Can you help me? 

[musicmaster]

You might help yourself by publishing which Prestashop version you are using and whether it is an upgrade or a new install.

 

It would also help if you gave us some idea what you believed caused these problems, If you had to wait 45 seconds you must have noticed it.

 

In the file \config\defines.inc.php you can set the flag .'_PS_DEBUG_PROFILING_' to true. Then Prestashop will show you how long its queries take.

[webmojito]

Thank ypu for thw reply. 

 

I use PS 1.6.0.14 (it's an upgrade) ..

 

 

Also this strange slow queries:

 

 

1. Executed 2h 22m 50s ago for 76.506919 sec on Database --> xx

Date: 2016-03-06 16:44:37 Query_time: 76.506919 Rows_examined: 0: Rows_sent 0 Lock_time: 0.000329

# Schema: xx Last_errno: 1160 Killed: 0 SELECT *, ( IF ('2016-03-06 16:43:20' >= `from` AND '2016-03-06 16:43:20' <= `to`, 1, 0) + IF (`id_group` = 1, 2, 0) + IF (`id_country` = 10, 4, 0) + IF (`id_currency` = 1, 8, 0) + IF (`id_shop` = 1, 16, 0) + IF (`id_customer` = 0, 32, 0)) AS `score` FROM `ps_specific_price` WHERE `id_product` IN (0, 81) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 10) AND `id_group` IN (0, 1) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2016-03-06 16:43:20' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2016-03-06 16:43:20' <= `to`) ) AND id_cart IN (0, 0) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= 1 ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1; 

------------------------------------------------------------------------------------------------- 

2. Executed 2h 22m 50s ago for 76.505251 sec on Database --> x

Date: 2016-03-06 16:44:37 Query_time: 76.505251 Rows_examined: 0: Rows_sent 0 Lock_time: 0.000386 

# Schema: xLast_errno: 1160 Killed: 0 SELECT *, ( IF ('2016-03-06 16:43:21' >= `from` AND '2016-03-06 16:43:21' <= `to`, 1, 0) + IF (`id_group` = 1, 2, 0) + IF (`id_country` = 10, 4, 0) + IF (`id_currency` = 1, 8, 0) + IF (`id_shop` = 1, 16, 0) + IF (`id_customer` = 0, 32, 0)) AS `score` FROM `ps_specific_price` WHERE `id_product` IN (0, 1012) AND `id_product_attribute` IN (0, 412493) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 10) AND `id_group` IN (0, 1) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2016-03-06 16:43:21' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2016-03-06 16:43:21' <= `to`) ) AND id_cart IN (0, 0) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= 1 ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1; 

------------------------------------------------------------------------------------------------- 

3. Executed 2h 22m 51s ago for 75.327918 sec on Database --> x

Date: 2016-03-06 16:44:36 Query_time: 75.327918 Rows_examined: 1: Rows_sent 1 Lock_time: 0.000168 

# Schema: xLast_errno: 1160 Killed: 0 SELECT `name`, `meta_title`, `meta_description`, `meta_keywords`, `description_short` FROM `ps_product` p LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.id_shop = 1 ) INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1) WHERE pl.id_lang = 5 AND pl.id_product = 965 AND product_shop.active = 1 LIMIT 1;

 

 

how do I know where they come from these queries?

 

Can you help me?