Problem with script execution time in database

[jirislav]

Hi there!

 

I'm really confused what I have to do now, because the hosting server is having the following settings without possibility to change these:

• proxy limit is 60 seconds
• Max_execution_time 90 seconds

Their feedback have recommended to call the script by a Cron, but I'm not even a newbie in this, I have actually never heard what Cron is or what is it supposed to do.

 

Here is the script that takes so long time to execute, so it is cut in the middle of its execution & this is doing really mess in the administration module. F.e. after every change of a product (only rewriting a description) is the system lagged for about 15 - 30 minutes.

> > SELECT p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as
> > quantity, MAX(product_attribute_shop.`id_product_attribute`) id_product_
> > attribute,
> > 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 4 DAY
> > )
> > ) > 0 AS new
> > 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_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_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 
> > JOIN ps_stock_available stock
> > ON (stock.id_product = p.id_product AND stock.id_product_attribute = 0 AND 
> > stock.id_shop = 1 )
> > WHERE p.`id_manufacturer` = 19
> > AND product_shop.`active` = 1
> > AND product_shop.`visibility` IN ("both", "catalog")
> > 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`) INNER JOIN `ps_category` ca ON cp.`
> > id_category` = ca.`id_category` AND ca.`active` = 1
> > WHERE cg.`id_group` = 1
> > )
> > GROUP BY product_shop.id_product
> > ORDER BY pl.`name` asc
> > LIMIT 0,60 
> > 

The hosting server have also told us that this script is being called every 2 - 10 minutes every day! It's just crazy nonstop script that has never enough data! :-D

 

What am I supposed to do?

 

Really thanks for your response. 

 

(Just you to understand my feelings: I am a junior programmer, started with C#, continued with Java & now I'm doing some PHP [well, a lot of PHP] .. I know something about jQuery to have dynamic pages & also javascript too. 

- to be clear - I don't really understand what is the script shown above really doing. I don't even know which file calls it so that I could try deleting it & disabling some modules which are executing that script, in other words - I'm screwed - e-shop doesn't work .. nor the orders ..)

 

So once again, I'm really glad you help me :-)

 

Thanks!

[vekia]

how many products per page your shop displays? (product per page option under preferences > products tab in your back office)

[jirislav]

how many products per page your shop displays? (product per page option under preferences > products tab in your back office)

 

It's 60 products per page & 10 products for comparison. Do you think decreasing that value should solve it?

[jirislav]

I've also noticed that 

 DATEDIFF(
> > product_shop.`date_add`,
> > DATE_SUB(
> > NOW(),
> > INTERVAL 4 DAY
> > )

is related to the number of days in which is a product marked as new. (Also preferences -> product tab) .. If I set the value to 0, the problem should be solved, wouldn't be?

[vekia]

Yeah, that's what I thought.

LIMIT 0,60

 

decreasing number of products per page should improve performance, of store, and also of this sql query

[jirislav]

Yeah, that's what I thought.

 

decreasing number of products per page should improve performance, of store, and also of this sql query

Thanks m8. Looks like it works fine. Now I'm waiting for response of the hosting for confimation of that problem.

I'll mark this solved as soon as it'll be confirmed.

 

BTW, how can I thank you? I could send you 1$  :-D .. if everyone did that, imagine what would you earn a day

[vekia]

BTW, how can I thank you? I could send you 1$  :-D .. if everyone did that, imagine what would you earn a day

you don't have to  

i'm enjoyin fact, that it works now hope that you host will confirm that

 

but in fact, if everyone would just do that I'd be a millionaire

[jirislav]

you don't have to

i'm enjoyin fact, that it works now hope that you host will confirm that

 

but in fact, if everyone would just do that I'd be a millionaire

So let me send you at least 2$ .. it's like invite for a drink ;D .. just publish on your profile your account where should all these payments go to & you're a millionare!