Jump to content
BigWebEU

[SOLVED] PRESTASHOP 1.7.6.1 VERY SLOW on products query

Recommended Posts

Hi guys ,

I hope this could be fixed ASAP .

I have a shop (6K products) migrated from 1.5 ->1 .7 , which seems to work just fine except that during the load of products selection query takes 16-30 seconds . (in this example popular products - limited to 8 results)

i tried running same queries in SQL - and it takes 16-20 seconds. 


However - if i remove "ORDER BY cp.`position` ASC"  statement - then it becomes 0.25 secods.

BOX: 4CPU XEON/ 16GB RAM/ SSD/ UBUNTU 18, MYSQL with all tables  Innodb 5.7.27 / PHP 7.2 / Mysql optimized with MySQLtuner

Any thoughts ? I would appreciate if someone could help me on this as shop goes live next week and im stuck :(

in DEBUG i see slowest queries:

 

SELECT SQL_NO_CACHE p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity, IFNULL(product_attribute_shop.id_product_attribute, 0) AS 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`, image_shop.`id_image` id_image,
il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
DATEDIFF(product_shop.`date_add`, DATE_SUB("2019-09-25 00:00:00",
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_shop` product_attribute_shop
ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1)
LEFT JOIN ps_stock_available stock
ON (stock.id_product = `p`.id_product AND stock.id_product_attribute = 0 AND stock.id_shop = 1  AND stock.id_shop_group = 0  )
LEFT JOIN `ps_category_lang` cl
ON (product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = 7 AND cl.id_shop = 1 )
LEFT JOIN `ps_product_lang` pl
ON (p.`id_product` = pl.`id_product`
AND pl.`id_lang` = 7 AND pl.id_shop = 1 )
LEFT JOIN `ps_image_shop` image_shop
ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=1)
LEFT JOIN `ps_image_lang` il
ON (image_shop.`id_image` = il.`id_image`
AND il.`id_lang` = 7)
LEFT JOIN `ps_manufacturer` m
ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` = 1
AND cp.`id_category` = 1 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ORDER BY cp.`position` ASC
LIMIT 0,8

	16218.3 	26822664 	Yes 		/classes/Category.php:1047

SELECT SQL_NO_CACHE p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) AS quantity, IFNULL(product_attribute_shop.id_product_attribute, 0) AS 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`, image_shop.`id_image` id_image,
il.`legend` as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default,
DATEDIFF(product_shop.`date_add`, DATE_SUB("2019-09-25 00:00:00",
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_shop` product_attribute_shop
ON (p.`id_product` = product_attribute_shop.`id_product` AND product_attribute_shop.`default_on` = 1 AND product_attribute_shop.id_shop=1)
LEFT JOIN ps_stock_available stock
ON (stock.id_product = `p`.id_product AND stock.id_product_attribute = 0 AND stock.id_shop = 1  AND stock.id_shop_group = 0  )
LEFT JOIN `ps_category_lang` cl
ON (product_shop.`id_category_default` = cl.`id_category`
AND cl.`id_lang` = 7 AND cl.id_shop = 1 )
LEFT JOIN `ps_product_lang` pl
ON (p.`id_product` = pl.`id_product`
AND pl.`id_lang` = 7 AND pl.id_shop = 1 )
LEFT JOIN `ps_image_shop` image_shop
ON (image_shop.`id_product` = p.`id_product` AND image_shop.cover=1 AND image_shop.id_shop=1)
LEFT JOIN `ps_image_lang` il
ON (image_shop.`id_image` = il.`id_image`
AND il.`id_lang` = 7)
LEFT JOIN `ps_manufacturer` m
ON m.`id_manufacturer` = p.`id_manufacturer`
WHERE product_shop.`id_shop` = 1
AND cp.`id_category` = 1 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") ORDER BY cp.`position` ASC
LIMIT 0,8

	15988.6 	26822664 	Yes 		/classes/Category.php:1047

 

Some screens with MySQL explanations :

image.thumb.png.9da113835f12d8659e9056d4f138b4cd.png

WITHOUT ORDER BY:

image.thumb.png.eebdffc0e66563d898a1bad65ecd2aac.png

 

 

 

 

image.png

image.png

Edited by BigWebEU (see edit history)

Share this post


Link to post
Share on other sites

Just realized, that if images are not selected - query is also fast !

Removing these lines :

    LEFT JOIN `ps_image_shop` image_shop ON ( image_shop.`id_product` = p.`id_product` AND image_shop.cover = 1 AND image_shop.id_shop = 1 )
    LEFT JOIN `ps_image_lang` il ON ( image_shop.`id_image` = il.`id_image` AND il.`id_lang` = 7 )

 

Any idea what is the real cause for this ?

Share this post


Link to post
Share on other sites

Sorry, they moved the buglist to github.

But its pretty much straightforward - just add "PRIMARY KEY (`id_image`, `id_shop`)" to the table manually.

In fact what i would suggest now - get SQL form new fresh install for your version , and compare that to your current tables. If they are not the same - change your current tables/indexes to match the new structure.

Share this post


Link to post
Share on other sites

Thanks, 

sorry for the stupid question, how do I compare the tables?

Share this post


Link to post
Share on other sites

Thanks, 

sorry for the question, how do I compare the tables?

Share this post


Link to post
Share on other sites

This is my website:

 <pma:table name="ps_image">
                CREATE TABLE `ps_image` (
                  `id_image` int(10) unsigned NOT NULL AUTO_INCREMENT,
                  `id_product` int(10) unsigned NOT NULL,
                  `position` smallint(2) unsigned NOT NULL DEFAULT '0',
                  `cover` tinyint(1) unsigned DEFAULT NULL,
                  PRIMARY KEY (`id_image`),
                  UNIQUE KEY `idx_product_image` (`id_image`,`id_product`,`cover`),
                  UNIQUE KEY `id_product_cover` (`id_product`,`cover`),

                  KEY `image_product` (`id_product`)
                ) ENGINE=InnoDB AUTO_INCREMENT=21323 DEFAULT CHARSET=utf8;

 </pma:table>

and this is prestashop 1.7.6.3 table

 <pma:table name="ps_image">
                CREATE TABLE `ps_image` (
                  `id_image` int(10) unsigned NOT NULL AUTO_INCREMENT,
                  `id_product` int(10) unsigned NOT NULL,
                  `position` smallint(2) unsigned NOT NULL DEFAULT '0',
                  `cover` tinyint(1) unsigned DEFAULT NULL,
                  PRIMARY KEY (`id_image`),
                  UNIQUE KEY `id_product_cover` (`id_product`,`cover`),
                  UNIQUE KEY `idx_product_image` (`id_image`,`id_product`,`cover`),

                  KEY `image_product` (`id_product`)
                ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;

 </pma:table>

Share this post


Link to post
Share on other sites

Well, seems like indexing is not your problem then :(  You've got proper structure, so keep looking

Share this post


Link to post
Share on other sites

Hello, thanks, i found in structure some varchar with different lenght from standard prestashop db structure, i thinks form update problem, but i don't think this can impact on speed.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More