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