Slow query with SQL_CALC_FOUND_ROWS in backoffice products listing

[erlaco]

PS Version 1.7.8.7

Ciao everyone,
in my backoffice I have very slow query (+12 secs) for products (242.200 aproximately) listing page , the other perfomances are ok.

I checked that the "SQL_CALC_FOUND_ROWS" slow down the entire query and I don't know how to remove it (and if it's possibile to do it without compromising the functionality) 

 

Here is the full query that appears on "show query" button

Quote

SELECT SQL_CALC_FOUND_ROWS p.`id_product`  AS `id_product`,
 p.`reference`  AS `reference`,
 sa.`price`  AS `price`,
 p.`id_shop_default`  AS `id_shop_default`,
 p.`is_virtual`  AS `is_virtual`,
 pl.`name`  AS `name`,
 pl.`link_rewrite`  AS `link_rewrite`,
 sa.`active`  AS `active`,
 shop.`name`  AS `shopname`,
 image_shop.`id_image`  AS `id_image`,
 cl.`name`  AS `name_category`,
 0 AS `price_final`,
 pd.`nb_downloadable`  AS `nb_downloadable`,
 sav.`quantity`  AS `sav_quantity`,
 IF(sav.`quantity`<=0, 1, 0) AS `badge_danger` 
FROM  `ps_product` p 
 LEFT JOIN `ps_product_lang` pl ON (pl.`id_product` = p.`id_product` AND pl.`id_lang` = 6 AND pl.`id_shop` = 1) 
 LEFT JOIN `ps_stock_available` sav ON (sav.`id_product` = p.`id_product` AND sav.`id_product_attribute` = 0 AND sav.id_shop = 1  AND sav.id_shop_group = 0 ) 
 JOIN `ps_product_shop` sa ON (p.`id_product` = sa.`id_product` AND sa.id_shop = 1) 
 LEFT JOIN `ps_category_lang` cl ON (sa.`id_category_default` = cl.`id_category` AND cl.`id_lang` = 6 AND cl.id_shop = 1) 
 LEFT JOIN `ps_category` c ON (c.`id_category` = cl.`id_category`) 
 LEFT JOIN `ps_shop` shop ON (shop.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` i ON (i.`id_image` = image_shop.`id_image`) 
 LEFT JOIN `ps_product_download` pd ON (pd.`id_product` = p.`id_product`) 
WHERE (1 AND state = 1)
 
ORDER BY  `id_product` desc
 
LIMIT 0, 20
;

Any suggestions ?

[LuisCarlosGonzález]

Hi @erlaco,
Same here, have you been able to come to a conclusion or find a solution?

[El Patron]

SQL_CALC_FOUND_ROWS is deprecated (as of MySQL 8.0.17) and performs poorly on large datasets.

This behavior is inherent to Symfony grid pagination logic used by PrestaShop.

• It causes MySQL to scan all rows, even if you're only showing a paginated subset.
• When combined with complex JOINs (as in PrestaShop's product grid), it increases execution time significantly.
• Product list queries involve numerous JOINs to pull names, categories, prices, combinations, etc.

Fixes in order of ease:

Consider setting innodb_buffer_pool_size to 70–80% of your server’s RAM.  Here is guide on how to calculate and set this value in my.cnf or my.ini.

There was a module I used on client shops, ArchiveBox but it is not on addons and developers website does not have way to buy it, see here.  You may want to contact them on how to purchase.

Note:  mysql while one of the most important aspects of performance is overlooked and out of box hosting has basic my.cnf, no cache etc.