Jump to content

Mysql query optimization


Recommended Posts

One of our customers says that his wesbsite based on prestashop is very slow,

 

Investigating the issue i found  that query below is which it takes more than 2 seconds and it os the slowest one  :

 

SELECT p.id_product, p.ean13, p.reference, p.id_category_default, p.on_sale, p.quantity, p.minimal_quantity, p.price, p.wholesale_price, p.quantity_discount, p.show_price, p.condition, product_shop.on_sale, product_shop.id_category_default, product_shop.minimal_quantity, product_shop.price, product_shop.wholesale_price, product_shop.show_price, product_shop.condition, product_shop.indexed, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`available_later`, pl.`link_rewrite`, pl.`name`, MAX(image_shop.`id_image`) id_image, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),INTERVAL 20 DAY)) > 0 AS new, product_shop.price AS orderprice FROM  `ps_product` p  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_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 2 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) WHERE product_shop.`id_shop` = 1  AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") AND p.`id_product` IN ( SELECT cp.`id_product` FROM `ps_category_product` cp WHERE cp.id_category IN (274, 275, 276, 318, 277, 278, 300, 301, 279, 280, 281, 282, 283, 284, 285, 286, 287, 288, 289, 292, 295, 209)) GROUP BY product_shop.id_product ORDER BY p.`date_add` DESC Limit 0, 6;

 

I rewitre it by removing the IN ( SELECT ......)  put at the end.

 

( SELECT cp.`id_product` FROM `ps_category_product` cp WHERE cp.id_category IN (274, 275, 276, 318, 277, 278, 300, 301, 279, 280, 281, 282, 283, 284, 285, 286, 287, 288, 289, 292, 295, 209)

 

and replace it whith a join between ps_category_product and ps_product tables as follows :

 

SELECT p.id_product, p.ean13, p.reference, p.id_category_default, p.on_sale, p.quantity, p.minimal_quantity, p.price, p.wholesale_price, p.quantity_discount, p.show_price, p.condition, product_shop.on_sale, product_shop.id_category_default, product_shop.minimal_quantity, product_shop.price, product_shop.wholesale_price, product_shop.show_price, product_shop.condition, product_shop.indexed, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, MAX(product_attribute_shop.id_product_attribute) id_product_attribute, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`available_later`, pl.`link_rewrite`, pl.`name`, MAX(image_shop.`id_image`) id_image, DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),INTERVAL 20 DAY)) > 0 AS new, product_shop.price AS orderprice FROM  `ps_product` p  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_product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = 2 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) join `ps_category_product` cp on p.`id_product`=cp.`id_product` WHERE product_shop.`id_shop` = 1  AND product_shop.`active` = 1 AND product_shop.`visibility` IN ("both", "catalog") and  cp.id_category IN (274, 275, 276, 318, 277, 278, 300, 301, 279, 280, 281, 282, 283, 284, 285, 286, 287, 288, 289, 292, 295, 209) GROUP BY product_shop.id_product ORDER BY p.`date_add` DESC Limit 0, 6;

 

 

This version is exeuted in 0.01 sec.

 

Is it possibile to rewrite the query in the future version as porposed  ?

 

Thanks.

Link to comment
Share on other sites

No Idea about the module where the query  resides.

The problem arises when we migrated mysql from 5.5 to 5.6 versions.

The same quwry run very fast on 5.5 version ( 0.03 sec) , but on 5.6 it takes 4 seconds and sometimes 8 seconds.

Mysql changed  how to choice of the executio plan of the same query.

 

I solved the issue by adding a new index on ps_product_shop table on id_product field.

 

Now the table structure is :

 

CREATE TABLE `ps_product_shop` (
  `id_product` int(10) unsigned NOT NULL,
  `id_shop` int(10) unsigned NOT NULL,
  `id_category_default` int(10) unsigned DEFAULT NULL,
  `id_tax_rules_group` int(11) unsigned NOT NULL,
  `on_sale` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `online_only` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `ecotax` decimal(17,6) NOT NULL DEFAULT '0.000000',
  `minimal_quantity` int(10) unsigned NOT NULL DEFAULT '1',
  `price` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `wholesale_price` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `unity` varchar(255) DEFAULT NULL,
  `unit_price_ratio` decimal(20,6) NOT NULL DEFAULT '0.000000',
  `additional_shipping_cost` decimal(20,2) NOT NULL DEFAULT '0.00',
  `customizable` tinyint(2) NOT NULL DEFAULT '0',
  `uploadable_files` tinyint(4) NOT NULL DEFAULT '0',
  `text_fields` tinyint(4) NOT NULL DEFAULT '0',
  `active` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `redirect_type` enum('','404','301','302') NOT NULL DEFAULT '',
  `id_product_redirected` int(10) unsigned NOT NULL DEFAULT '0',
  `available_for_order` tinyint(1) NOT NULL DEFAULT '1',
  `available_date` date NOT NULL DEFAULT '0000-00-00',
  `condition` enum('new','used','refurbished') NOT NULL DEFAULT 'new',
  `show_price` tinyint(1) NOT NULL DEFAULT '1',
  `indexed` tinyint(1) NOT NULL DEFAULT '0',
  `visibility` enum('both','catalog','search','none') NOT NULL DEFAULT 'both',
  `cache_default_attribute` int(10) unsigned DEFAULT NULL,
  `advanced_stock_management` tinyint(1) NOT NULL DEFAULT '0',
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  `pack_stock_type` int(11) unsigned DEFAULT '3',
  `mpr_pp_import_line` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_product`,`id_shop`),
  KEY `id_category_default` (`id_category_default`),
  KEY `date_add` (`date_add`,`active`,`visibility`),
  KEY `indexed` (`indexed`,`active`,`id_product`),
  KEY `idx1` (`id_product`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

The added index is idx1. It seems to be a "duplicate" of the PRIMARY KEY as this one has the most left field on id_product. 

But it works. And now. alos on 5.6 version, the query is very fast.

 

STofa

Link to comment
Share on other sites

×
×
  • Create New...