Michal Kittay-Jurdyg Posted May 7, 2015 Share Posted May 7, 2015 Hi All, We have a small PrestaShop store with nor more than 1000 products. W just upgraded to the latest version that is 1.6, but something is not right. Our server has a good spec: 64G of RAM and Intel Xeon 8x cores CPU. PHP has 512MB allocated memory and Presta using Memcache as well. The home page and categories are loading very quick below 2 sec. However, most of the product pages loads up to even 6-8 seconds. We found the problem that was very slow SQL query. There is a lot of joins and sorting. When I'm trying to run this query from the server, it takes 6 seconds or more. Most of our products are configurable so this problem is only related to configurable products. For simple products loading time is below 1 sec. Can you advise what we can with this issue? Our very slow SQL query: SELECT SQL_NO_CACHE p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, MAX(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, MAX(il.`legend`) as legend, m.`name` AS manufacturer_name, cl.`name` AS category_default, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(), INTERVAL 20 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_attribute = 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_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` = 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` = 6 AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") GROUP BY product_shop.id_product ORDER BY pl.`name` asc LIMIT 0,12; Link to comment Share on other sites More sharing options...
JPresta.com Posted May 7, 2015 Share Posted May 7, 2015 There are probably some optimisations to do on your database. Did you try without the "ORDER BY" clause? If it's faster, just add an index on the 'name' column. Link to comment Share on other sites More sharing options...
Michal Kittay-Jurdyg Posted May 7, 2015 Author Share Posted May 7, 2015 Thanks for your reply, unfortunately, didn't help a lot. Without order by I have just 0.08 sec faster so not a big difference. Link to comment Share on other sites More sharing options...
JPresta.com Posted May 7, 2015 Share Posted May 7, 2015 You need to find a MySQL expert :-) If I were you I would reduce the query until I find what is making it slow. By reducing I mean remove order by, some left join, etc. There is also an "EXPLAIN" command but as I remember it's difficult to understand. Link to comment Share on other sites More sharing options...
PrestaHeroes USA Posted May 7, 2015 Share Posted May 7, 2015 You can get useful information that will help. Using phpMyAdmin display run-time stats of your mysql engine. For poorly configured mysql the report will highlight in red. Contact your hosting about changing if you do not know how. (or use google) Find 'Status' menu tab, it may differ on your release of phpMyAdmin. http://screencast.com/t/Ra8uTJEL Example data Innodb. http://screencast.com/t/nxOcuGnOT Link to comment Share on other sites More sharing options...
isabella_brown Posted May 9, 2015 Share Posted May 9, 2015 Hi Michal, Why don't you try store procedures? I guess with store procedures loading time may be improve. Link to comment Share on other sites More sharing options...
Recommended Posts