Advice needed on slow page loading due to long database queries

[tommynay]

Hello,

 

My site is running very slow, my hosting company Vidahost have set up DSO and ACP instead of suphp which are supposed to make the site run quicker. Can anyone help or advise what needs to be done to improve the page speed/bloated database queries? This is what my host has to say: 

 

"This issue isn't related to images but your  database structure. There is a query that  runs for 20 seconds which returns 8 results  - it's just incredibly inefficient  programming. I've pushed the MySQL buffers  as far as they can safely go on this  server, but you might need to get a  Prestashop developer to take a look at  this. You can also ask on the Prestashop  forums to see if anyone else has had the  same issue - make sure you show them the query that's being run though."

QUERY running on the front page:

 

Copying to tmp table | SELECT p.*, 

 

product_shop.*, stock.out_of_stock, IFNULL

 

(stock.quantity, 0) as quantity, MAX

 

(product_attribute_shop.id_product_attribut

 

e) id_product_attribute, 

 

product_attribute_shop.minimal_quantity AS 

 

product_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 0

                                            

 

    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_attribut

 

e = 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_attrib

 

ute, 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` = 1 AND cl.id_shop = 1 )

                                LEFT JOIN 

 

`ps_product_lang` pl

                                        ON 

 

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

                                        AND 

 

pl.`id_lang` = 1 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` = 1)

                                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` ASC

                        LIMIT 0,8

 

Queries like this are what is killing the 

 

performance of the store.

 

[bellini13]

Prestashop version, theme being used, third party modules installed... help us help you

[jasmynn]

I'm having the same issues when upgrading from 1.4.4 to 1.5.6 

I have only the basic modules installed, no third party modules.  We ran the database backup after updating the site because no products were showing up after upgrade.

Now all of the products are there, but the categories take extremely long to load over 20 seconds.  My hosting company took a look and said my sql database tables were taking too long.  What can I do?

 

 

See for yourself at

www.oraiasportsetc.com