Site extremely slow - a lot of recources requested to the server

[edoluz]

Hello all.

I'm Edo, from Italy. And... I'm not a programmer...

Our website www.trophyhunt.it (PS 1.5.6.2) was working great until few days ago, when our hosting provider changed the server IP. We started having some speed problems until today, then they (provider) says our website is requesting a lot of resources to the server. They server that the query below are responsibles for these requests... Can you please help us understanding where the problem is and solve it? We're stuck in this problem.

 

Thank you.

Edo

SELECT p.*, product_shop.*, product_shop.id_category_default, pl.*, MAX(image_shop.`id_image`) id_image, il.legend, m.name manufacturer_name, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL 20 DAY)) > 0 AS new, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity FROM `ps_category_product` cp LEFT JOIN ps_category c ON (c.id_category = cp.id_category) LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` 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_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 ) LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product AND pl.id_shop = 1 AND pl.id_lang = 5) 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 (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 5) LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") AND c.nleft >= 276 AND c.nright <= 277 AND c.active = 1 AND p.id_product IN (248,494,833,1732,1736,1943,1966,1967,1986,2152,2153,2778,2923,2957,3043,3621,3622,3623,3625,3672,3673,3674,3675,3676,3677,3680,3683,3684,3685,3871,3969,3973,3975,3976,3977,4718,4742,4749,4755,4756,4800,4905,4917,4950,4951,4953,4954,4956,4968,5030,5046,5047,5150,5155,5159,5228,5330,5548,5586,5603,5605,5609,5610,5611,5613,5614,5615,5616,5617,5618,5619,6044,6045,6046,6066,6067,6068,6074,6083,6161,6162,6163,6304,6309,6310,6325,6329,6330,6332,6336,6337,6342,6344,6345,6384,6392,6393,6394,6395,6396,6397,6398,6399,6401,6402,6403,6405,6406,6407,6408,6862,6863,6940,7137,7145,7171,7255) GROUP BY product_shop.id_product ORDER BY cp.position asc LIMIT 0,50

 

[edoluz]

Any kind of help is very appreciated!

[fitgura]

Hi!

well as I tested your site here (you can finf many page testers)

looks like the main reason is a redirection from your main domain name to the /shop/ flder where is your site available

I do not know how your ISP (and why) set it into a shop folder, but you must move the wjhole page into root

 

 

[edoluz]

1 hour ago, fitgura said:

Hi!

well as I tested your site here (you can finf many page testers)

looks like the main reason is a redirection from your main domain name to the /shop/ flder where is your site available

I do not know how your ISP (and why) set it into a shop folder, but you must move the wjhole page into root

 

 

Hi fitgura, thank you for your reply.

The website is in a folder because we have 2 shops in 2 folders. To link directly to the "shop" folder I acted using .htaccess, in this way below.

After your message I removed it, but the site is still slow or stuck!

Thank you again!

# Created by https://htaccessbuilder.com/
# Fri, 2nd February 2018
<IfModule mod_rewrite.c>

# Enable Rewrite Engine
RewriteEngine On
RewriteBase /

# Redirect root to /shop
RedirectMatch 301 ^/$ /shop/

# Redirect to www.
RewriteCond %{HTTP_HOST} ^trophyhunt.it$
RewriteRule (.*) http://www.trophyhunt.it/$1 [R=301,L] 

</IfModule>

 

[fitgura]

Honestly I do not know, something mixed in your host settings I"m sure

please check this

 

link

[hakeryk2]

Well, You should use EXPLAIN before this in phmyadmin

SELECT p.*, product_shop.*, product_shop.id_category_default, pl.*, MAX(image_shop.`id_image`) id_image, il.legend, m.name manufacturer_name, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL 20 DAY)) > 0 AS new, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity FROM `ps_category_product` cp LEFT JOIN ps_category c ON (c.id_category = cp.id_category) LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` 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_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 ) LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product AND pl.id_shop = 1 AND pl.id_lang = 5) 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 (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 5) LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") AND c.nleft >= 276 AND c.nright <= 277 AND c.active = 1 AND p.id_product IN (248,494,833,1732,1736,1943,1966,1967,1986,2152,2153,2778,2923,2957,3043,3621,3622,3623,3625,3672,3673,3674,3675,3676,3677,3680,3683,3684,3685,3871,3969,3973,3975,3976,3977,4718,4742,4749,4755,4756,4800,4905,4917,4950,4951,4953,4954,4956,4968,5030,5046,5047,5150,5155,5159,5228,5330,5548,5586,5603,5605,5609,5610,5611,5613,5614,5615,5616,5617,5618,5619,6044,6045,6046,6066,6067,6068,6074,6083,6161,6162,6163,6304,6309,6310,6325,6329,6330,6332,6336,6337,6342,6344,6345,6384,6392,6393,6394,6395,6396,6397,6398,6399,6401,6402,6403,6405,6406,6407,6408,6862,6863,6940,7137,7145,7171,7255) GROUP BY product_shop.id_product ORDER BY cp.position asc LIMIT 0,50

And send result here using screenshot.

[edoluz]

On 21/2/2018 at 2:33 PM, hakeryk2 said:

Well, You should use EXPLAIN before this in phmyadmin

SELECT p.*, product_shop.*, product_shop.id_category_default, pl.*, MAX(image_shop.`id_image`) id_image, il.legend, m.name manufacturer_name, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL 20 DAY)) > 0 AS new, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity FROM `ps_category_product` cp LEFT JOIN ps_category c ON (c.id_category = cp.id_category) LEFT JOIN `ps_product` p ON p.`id_product` = cp.`id_product` 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_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 ) LEFT JOIN ps_product_lang pl ON (pl.id_product = p.id_product AND pl.id_shop = 1 AND pl.id_lang = 5) 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 (image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 5) LEFT JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer) WHERE product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") AND c.nleft >= 276 AND c.nright <= 277 AND c.active = 1 AND p.id_product IN (248,494,833,1732,1736,1943,1966,1967,1986,2152,2153,2778,2923,2957,3043,3621,3622,3623,3625,3672,3673,3674,3675,3676,3677,3680,3683,3684,3685,3871,3969,3973,3975,3976,3977,4718,4742,4749,4755,4756,4800,4905,4917,4950,4951,4953,4954,4956,4968,5030,5046,5047,5150,5155,5159,5228,5330,5548,5586,5603,5605,5609,5610,5611,5613,5614,5615,5616,5617,5618,5619,6044,6045,6046,6066,6067,6068,6074,6083,6161,6162,6163,6304,6309,6310,6325,6329,6330,6332,6336,6337,6342,6344,6345,6384,6392,6393,6394,6395,6396,6397,6398,6399,6401,6402,6403,6405,6406,6407,6408,6862,6863,6940,7137,7145,7171,7255) GROUP BY product_shop.id_product ORDER BY cp.position asc LIMIT 0,50

And send result here using screenshot.

Hello, sorry for the uge delay in my answer but I do not receive any notification by the PS Forum for the replies (even if they are active ).

I'm sorry but I've not understood yout advice, can you please explain it?

In the meanwhile it seems the problem was due to some problems of the hosting provider... he made some optimizations... cleaned here and there in the DB and the website have been fixed immediately without any modification by my side...