[SOLVED] PRESTASHOP 1.7.6.1 VERY SLOW on products query

[BigWebEU]

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 :

WITHOUT ORDER BY:

 

 

 

 

Edited September 25, 2019 by BigWebEU (see edit history)

[BigWebEU]

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 ?

[BigWebEU]

OK, this one is solved!

In short - the Primary Keys on table ps_image_shop was not present after upgrade . Adding keys "PRIMARY KEY (`id_image`, `id_shop`)," solves the troubles - all product queqries are light fast now !

Full solution and bug report here: http://forge.prestashop.com/browse/BOOM-3650

 

[Gipielle]

Hi @BigWebEU the link is broken, do you have the full solution 

 

Thanks

[BigWebEU]

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.

[Gipielle]

Thanks, 

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

[Gipielle]

Thanks, 

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

[Gipielle]

Found how to diff database structure.

[Gipielle]

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>

[BigWebEU]

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

[Gipielle]

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.