MySQL Forums
Forum List  »  Optimizer & Parser

Can anyone help speed up this query?
Posted by: Search Team
Date: June 21, 2017 11:07AM

We host e-commerce sites. When the user enters keywords into the "search box" on the e-commerce site, a PHP script generates and executes a SQL query for the results.

The SQL query below is what is generated for the search phrase "copy paper".

Can anyone guide us to optimize this query?


SELECT `pinfo`.`sku`, `pinfo`.`vendor`,
IF(`pinfo`.`multi_level_pricing_check`=1, (`mlrp`.`cost`/`mlrp`.`min_qty`),
IF(CURDATE() >= `ds`.`expiration_date`, `ds`.`price`, `crp`.`contract_price`)) AS `price`,

CONCAT(IF(`pinfo`.`vendor` = 2, `desc_long`, `desc_short`),' ',`pinfo`.`sku`,' ',`category_1`,' ',`category_2`,' ',
`category_3`,' ',`category_4`,' ',IF(`pinfo`.`vendor` = 2, `sales_copy`, `desc_long`),' ',
IF(`dealer_sku` != '', `dealer_sku`, ''),' ',`manufacturer_name`,' ',`color`) AS `search_string`

FROM `product_info` `pinfo`
INNER JOIN `vendor_base_costs` `vbcc` ON `pinfo`.`sku`=`vbcc`.`sku`
LEFT JOIN `contract_price_13` AS `crp` ON `crp`.`sku`=`pinfo`.`sku`
LEFT JOIN `dealer_special` AS `ds` ON `ds`.`sku`=`pinfo`.`sku` AND CURDATE() BETWEEN `ds`.`start_date` AND `ds`.`expiration_date`
LEFT JOIN `multi_level_retail_price` AS `mlrp`
ON `mlrp`.`sku`=`pinfo`.`sku`
AND `mlrp`.`preferred_vendor`=`pinfo`.`preferred_vendor`
AND (`mlrp`.`cost` / `mlrp`.`min_qty`) > 0

WHERE (
(
(
MATCH(`desc_short`, `desc_long`, `desc_title`, `keywords`, `category_1`, `category_2`, `category_3`,
`category_4`, `manufacturer_name`, `color`) AGAINST ('+(>copy>copies)+(>paper>papers)' IN BOOLEAN MODE)
OR
MATCH(`desc_short`, `desc_long`, `desc_title`, `keywords`, `category_1`, `category_2`, `category_3`,
`category_4`, `manufacturer_name`, `color`) AGAINST ('"copy paper"' IN BOOLEAN MODE)
)
OR
(
REPLACE(`pinfo`.`sku`, `pinfo`.`manufacturer_prefix`, '') LIKE 'copy paper%'
)
OR
(
CONCAT(`manufacturer_name`,' ',`pinfo`.`sku`) LIKE '%copy%paper%'
OR
CONCAT(`pinfo`.`sku`,' ',`manufacturer_name`) LIKE '%copy%paper%'
)
OR
(
`pinfo`.`sku` LIKE 'copy paper' OR `pinfo`.`sku` LIKE 'copypaper' OR `pinfo`.`sku` LIKE 'copy paper'
)
)
AND
`pinfo`.`category_1` NOT LIKE 'Technology'
)
AND `pinfo`.`status` IN ('active','discontinued')
AND `pinfo`.`desc_title` != ''
AND `online_status` = 1
AND `custom_sku_enable` = 1
AND (CASE WHEN `pinfo`.`multi_level_pricing_check` = 1
THEN TRUE
ELSE IF(`pinfo`.`fixed_price` > 0, `pinfo`.`fixed_price`, `crp`.`contract_price`) > 0
END)
AND `pinfo`.`sku` NOT IN (SELECT `alias_sku` FROM `internal_alias_detail`)

GROUP BY `pinfo`.`sku`

ORDER BY
IF(`best_seller` > 0, 1, 0) DESC,

IF(
IF(
( LENGTH('copy paper') - LENGTH(replace('copy paper', ' ', '')) ) > 0
,
(
CONCAT(`manufacturer_name`,' ',`pinfo`.`sku`) LIKE '%copy%paper%'
OR
CONCAT(`pinfo`.`sku`,' ',`manufacturer_name`) LIKE '%copy%paper%'
)
,
FALSE
)
OR
(
`pinfo`.`sku` LIKE 'copy paper' OR `pinfo`.`sku` LIKE 'copypaper' OR `pinfo`.`sku` LIKE 'copy paper'
OR REPLACE(`pinfo`.`sku`,'-','') LIKE 'copy paper'
OR REPLACE(`pinfo`.`sku`,'-','') LIKE 'copypaper'
OR REPLACE(`pinfo`.`sku`,'-','') LIKE 'copy paper'
)
, 1 , 0
) DESC,

(
(CASE WHEN `desc_short` LIKE 'paper,%' OR `desc_short` LIKE 'papers%' THEN 4 ELSE 0 END)
+ (CASE WHEN MATCH(`category_4`) AGAINST ('+paper' IN BOOLEAN MODE)
OR MATCH(`category_3`) AGAINST ('+paper' IN BOOLEAN MODE)
OR MATCH(`category_2`) AGAINST ('+paper' IN BOOLEAN MODE)
OR MATCH(`category_1`) AGAINST ('+paper' IN BOOLEAN MODE)
OR MATCH(`category_4`) AGAINST ('+papers' IN BOOLEAN MODE)
OR MATCH(`category_3`) AGAINST ('+papers' IN BOOLEAN MODE)
OR MATCH(`category_2`) AGAINST ('+papers' IN BOOLEAN MODE)
OR MATCH(`category_1`) AGAINST ('+papers' IN BOOLEAN MODE) THEN 4 ELSE 0 END)
+ (CASE WHEN
IF(`pinfo`.`vendor` = 2
,MATCH(`desc_long`) AGAINST('"copy paper"' IN BOOLEAN MODE)
,MATCH(`desc_title`) AGAINST('"copy paper"' IN BOOLEAN MODE)
)
THEN 2 ELSE 0 END)
+ (CASE WHEN
IF(`pinfo`.`vendor` = 2
,MATCH(`desc_long`) AGAINST(+'copy +paper' IN BOOLEAN MODE)
,MATCH(`desc_title`) AGAINST('+copy +paper' IN BOOLEAN MODE)
)
THEN 1 ELSE 0 END)
) DESC,

IF(MATCH (`keywords`) AGAINST ('"copy paper"' IN BOOLEAN MODE), 1 , 0) DESC,

SUBSTR(`pinfo`.`desc_title`, 1, LOCATE('paper', `pinfo`.`desc_title`)) DESC


Here is the EXPLAIN output:

1 PRIMARY pinfo ref PRIMARY,id_UNIQUE,search_param_index,... new_search_index 10 const,const 23094 Using where; Using temporary; Using filesort
1 PRIMARY crp ref PRIMARY PRIMARY 27 aosware_spr_dealercommander_repository_replica.pinfo.sku 1 Using where
1 PRIMARY ds ref sku_index sku_index 28 aosware_spr_dealercommander_repository_replica.pinfo.sku 1 Using where
1 PRIMARY vbcc ref SKU_INDEX,sku_qty_cost_pricing_index SKU_INDEX 303 func 1 Using where; Using index
1 PRIMARY mlrp ref index_sku_edit_item index_sku_edit_item 27 aosware_spr_dealercommander_repository_replica.pinfo.sku 2 Using where
2 SUBQUERY internal_alias_detail system alias_sku 0 const row not found




btw, this site took out all of the painstaking indentation I added to make it readable. To see a readable version:

https://aoscommander.com/sqlquestion.html

Options: ReplyQuote


Subject
Views
Written By
Posted
Can anyone help speed up this query?
394
June 21, 2017 11:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.