A lot of product combinations slow down shop

[menace]

Hello everybody.

 

I have one big problem. I use PS 1.5.4. I have products (computers which you can comfigurate by yourself) with many attributes. I used to generate features but my e-shop got slow down. I looked at my hosting CPU and memory. Both of them are very high and busy. I contacted my webprovider. They told me that there is long string of question for database. Don´t you know why?

 

I really need help. It takes about 20 - 30 seconds to load page sometimes even more...

 

Thank you for your answers ..

[benjamin utterback]

Hi menace, what are you server specifications like memory, processors etc. Are you on a shared hosting?

[PiccoliniBabyStore]

I cannot answer the solution to your problem; but my website has been running extremely slow. I have about 50 personalized gifts with tons of customization per item.

 

Any help on this topic would be appreciated. Thanks!

 

piccolinibabystore dot com

[benjamin utterback]

I cannot answer the solution to your problem; but my website has been running extremely slow. I have about 50 personalized gifts with tons of customization per item.

 

Any help on this topic would be appreciated. Thanks!

 

piccolinibabystore dot com

 

Hi, I looked at your website and it is moving a little slow. What are your server specifications, shared hosting? memory ram? processors etc...

 

I know that you have 22 external Javascript scripts, 27 external stylesheets, and 29 external background images.

 

That's a lot of http requests. You can combine each of these scripts, sheets, and images into one. That will definitely decrease your loading time. Alternatively, you can up your server in necessary.

[SmartDataSoft]

Are you using any third party module to change your attribute to option or radio box.

[menace]

Hi all.

 

My server is multihosting with 512 MB memory limit, one virtual CPU. My web is www.levnehp.cz

 

Thank you for your replies )

[menace]

My hosting provider wrote me this:

 

horrible question on database:

 

 

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`, MAX

(image_shop.`id_image`) id_image, il.`legend`, m.`name` AS manufacturer_name,

DATEDIFF(

product_shop.`date_add`,

DATE_SUB(

NOW(),

INTERVAL 20 DAY

)

) > 0 AS new,

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` = 2 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` =

2

LEFT JOIN `ps_manufacturer` `m` ON m.`id_manufacturer` = p.`id_manufacturer`

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 )

WHERE (product_shop.`active` = 1) AND (product_shop.`visibility` IN ("both",

"catalog")) AND (

DATEDIFF(

product_shop.`date_add`,

DATE_SUB(

NOW(),

INTERVAL 20 DAY

)

) > 0) AND (p.`id_product` IN (

SELECT cp.`id_product`

FROM `ps_category_group` cg

LEFT JOIN `ps_category_product` cp ON (cp.`id_category` =

cg.`id_category`)

WHERE cg.`id_group` = 1))

GROUP BY product_shop.id_product

ORDER BY p.`date_add` DESC

LIMIT 5

[viktor123]

Hi, I also experience the same problem. I got msg from my hosting provider that this query should be optimized, because it is eating up my server process time. I ordered more memory for my VPS, but that's just a quick solution. Please clarify where this query comes from. If it is a module function, I may disactivate the module,...

 

My query that takes 19 seconds

 

SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS produ

ct_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`,

pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, MAX(image_shop.`id_image`) id_image,

il.`legend`, m.`name` AS manufacturer_name, cl.`name` AS category_default,

DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),

INTERVAL 30

DAY)) > 0 AS new, product_shop.price AS orderprice

FROM `ps_category_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_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_category_lang` cl

ON (product_shop.`id_category_default` = cl.`id_category`

AND cl.`id_lang` = 4 AND cl.id_shop = 1 )

LEFT JOIN `ps_product_lang` pl

ON (p.`id_product` = pl.`id_product`

AND pl.`id_lang` = 4 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 (image_shop.`id_image` = il.`id_image`

AND il.`id_lang` = 4)

LEFT JOIN `ps_manufacturer` m

ON m.`id_manufacturer` = p.`id_manufacturer`

WHERE product_shop.`id_shop` = 1

AND cp.`id_category` = 2 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") GROUP BY product_shop.id_product ORDER BY cp.`position` AS

C

LIMIT 0,1000000;