slow MySQL queries

[fishordog]

My store has been getting slower and slower, so i asked my host and they replied that i must have some issues with database and need to check and optimize it. Any suggestions how to do it and why it could be slow? Some of the slow queries below:

Executed 10 min ago for 3 sec on Database --> presta_123. 
SELECT *, ( IF ('2013-12-19 12:59:48' >= `from` AND '2013-12-19 12:59:48' <= `to`, 1, 0) + IF (`id_group` = 1, 2, 0) + IF (`id_country` = 21, 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, 2497) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 21) AND `id_group` IN (0, 1) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 12:59:48' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 12:59:48' <= `to`) ) AND id_cart IN (0, 0) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= IF(id_product_attribute=0,0 ,1) ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1 
Executed 9 min ago for 10 sec on Database --> presta_123. 
SELECT `id_product`, `id_product_attribute` FROM `ps_specific_price` WHERE `id_shop` IN(0, 1) AND `id_currency` IN(0, 1) AND `id_country` IN(0, 21) AND `id_group` IN(0, 1) AND `id_customer` IN(0, 0) AND `from_quantity` = 1 AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 13:03:00' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 13:03:00' <= `to`) ) AND `reduction` > 0
Executed 6 min ago for 1 sec on Database --> presta_123. 
SELECT *, ( IF ('2013-12-19 13:06:53' >= `from` AND '2013-12-19 13:06:53' <= `to`, 1, 0) + IF (`id_group` = 1, 2, 0) + IF (`id_country` = 21, 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, 1352) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 21) AND `id_group` IN (0, 1) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 13:06:53' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 13:06:53' <= `to`) ) AND id_cart IN (0, 0) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= IF(id_product_attribute=0,0 ,1) ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1 
Executed 3 min ago for 1 sec on Database --> presta_123. 
SELECT *, ( IF ('2013-12-19 13:06:55' >= `from` AND '2013-12-19 13:06:55' <= `to`, 1, 0) + IF (`id_group` = 1, 2, 0) + IF (`id_country` = 21, 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, 2497) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 21) AND `id_group` IN (0, 1) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 13:06:55' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 13:06:55' <= `to`) ) AND id_cart IN (0, 0) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= IF(id_product_attribute=0,0 ,1) ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1 
Executed 3 min ago for 10 sec on Database --> presta_123. 
SELECT `id_product`, `id_product_attribute` FROM `ps_specific_price` WHERE `id_shop` IN(0, 1) AND `id_currency` IN(0, 1) AND `id_country` IN(0, 21) AND `id_group` IN(0, 1) AND `id_customer` IN(0, 0) AND `from_quantity` = 1 AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 13:09:09' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 13:09:09' <= `to`) ) AND `reduction` > 0
Executed 0 min ago for 8 sec on Database --> presta_123. 
SELECT `id_product`, `id_product_attribute` FROM `ps_specific_price` WHERE `id_shop` IN(0, 1) AND `id_currency` IN(0, 1) AND `id_country` IN(0, 21) AND `id_group` IN(0, 1) AND `id_customer` IN(0, 0) AND `from_quantity` = 1 AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 13:09:20' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 13:09:20' <= `to`) ) AND `reduction` > 0
Executed 0 min ago for 1 sec on Database --> presta_123.

[Dh42]

Do you have a lot of products on sale? Are you running the specials block on your site?

[fishordog]

Do you have a lot of products on sale? Are you running the specials block on your site?

 I listed all my  products with msrp prices and then created catalog prices rules. Some of them have "Leave base price" option on and some off. 

 Specials block was enabled but i wasn't using so i disabled it few minutes ago. I'm not sure but it seems like site is faster after i disabled it, or maybe it's just coincidence. 

 

Do you think it was specials block(even if it wasn't on the front page) or because all my products have price rule?

[Dh42]

I would be willing to bet that the specials block is the reason. The specials block runs a query that can be slow when you have a lot of on sale products.  I would give it a couple days and ask your host to out put the slow queries again and see if anything has changed. 

[fishordog]

I would be willing to bet that the specials block is the reason. The specials block runs a query that can be slow when you have a lot of on sale products.  I would give it a couple days and ask your host to out put the slow queries again and see if anything has changed. 

They just ran a query to show most recent slow queries 

Slow queries were found:
Executed 75 min ago for 4 sec on Database --> presta123. 
SELECT *, ( IF ('2013-12-19 20:19:52' >= `from` AND '2013-12-19 20:19:52' <= `to`, 1, 0) + IF (`id_group` = 1, 2, 0) + IF (`id_country` = 21, 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, 1352) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 21) AND `id_group` IN (0, 1) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 20:19:52' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 20:19:52' <= `to`) ) AND id_cart IN (0, 0) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= IF(id_product_attribute=0,0 ,1) ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1 
Executed 64 min ago for 3 sec on Database --> presta123. 
SELECT *, ( IF ('2013-12-19 20:19:56' >= `from` AND '2013-12-19 20:19:56' <= `to`, 1, 0) + IF (`id_group` = 1, 2, 0) + IF (`id_country` = 21, 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, 2497) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 21) AND `id_group` IN (0, 1) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 20:19:56' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 20:19:56' <= `to`) ) AND id_cart IN (0, 0) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= IF(id_product_attribute=0,0 ,1) ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1
Executed 52 min ago for 3 sec on Database --> presta123.
SELECT *, ( IF ('2013-12-19 20:45:59' >= `from` AND '2013-12-19 20:45:59' <= `to`, 1, 0) + IF (`id_group` = 1, 2, 0) + IF (`id_country` = 21, 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, 1352) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 21) AND `id_group` IN (0, 1) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 20:45:59' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 20:45:59' <= `to`) ) AND id_cart IN (0, 0) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= IF(id_product_attribute=0,0 ,1) ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1
Executed 38 min ago for 2 sec on Database --> presta123.
SELECT *, ( IF ('2013-12-19 20:46:02' >= `from` AND '2013-12-19 20:46:02' <= `to`, 1, 0) + IF (`id_group` = 1, 2, 0) + IF (`id_country` = 21, 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, 2497) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 21) AND `id_group` IN (0, 1) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 20:46:02' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 20:46:02' <= `to`) ) AND id_cart IN (0, 0) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= IF(id_product_attribute=0,0 ,1) ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1
Executed 35 min ago for 2 sec on Database --> presta123.
SHOW TABLE STATUS FROM `presta123
Executed 31 min ago for 2 sec on Database --> presta123.
SHOW TABLE STATUS FROM `presta123
Executed 25 min ago for 2 sec on Database --> presta123.
SELECT *, ( IF ('2013-12-19 21:02:11' >= `from` AND '2013-12-19 21:02:11' <= `to`, 1, 0) + IF (`id_group` = 3, 2, 0) + IF (`id_country` = 21, 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, 1085) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 21) AND `id_group` IN (0, 3) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 21:02:11' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 21:02:11' <= `to`) ) AND id_cart IN (0, 124) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= IF(id_product_attribute=0,1 ,1) ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1
Executed 22 min ago for 3 sec on Database --> presta123.
SELECT *, ( IF ('2013-12-19 21:02:14' >= `from` AND '2013-12-19 21:02:14' <= `to`, 1, 0) + IF (`id_group` = 3, 2, 0) + IF (`id_country` = 21, 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, 1240) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 21) AND `id_group` IN (0, 3) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 21:02:14' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 21:02:14' <= `to`) ) AND id_cart IN (0, 124) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= IF(id_product_attribute=0,1 ,1) ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1
Executed 22 min ago for 2 sec on Database --> presta123.
SELECT *, ( IF ('2013-12-19 21:02:18' >= `from` AND '2013-12-19 21:02:18' <= `to`, 1, 0) + IF (`id_group` = 3, 2, 0) + IF (`id_country` = 21, 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, 1361) AND `id_product_attribute` IN (0, 0) AND `id_shop` IN (0, 1) AND `id_currency` IN (0, 1) AND `id_country` IN (0, 21) AND `id_group` IN (0, 3) AND `id_customer` IN (0, 0) AND ( (`from` = '0000-00-00 00:00:00' OR '2013-12-19 21:02:18' >= `from`) AND (`to` = '0000-00-00 00:00:00' OR '2013-12-19 21:02:18' <= `to`) ) AND id_cart IN (0, 129) AND IF(`from_quantity` > 1, `from_quantity`, 0) <= IF(id_product_attribute=0,1 ,1) ORDER BY `id_product_attribute` DESC, `from_quantity` DESC, `id_specific_price_rule` ASC, `score` DESC LIMIT 1

[Dh42]

Those are specific price related. What are your sever specs? 

[fishordog]

Its shared server so  guess shouldn't expect much. 

Don't really know all specs besides 20gb hdd and w/e i can see under prestashop config info:

Server information: Linux  x86_64

Server software version: Apache

PHP version: 5.3.28

Memory limit: 128M

Max execution time: 45

 

They can't even increase server time-out, because it's shared. I tried to make memcached work but i think it's still buggy in 1.5, it was just making everything slower. Would be nice if it was working

Anyway,  store seems to be loading faster without specials module. Do you think if i remove price rules it can help too?