> MySQL version?
Server version: 5.6.35 - MySQL Community Server (GPL)
> How much RAM is available to MySQL?
I'm not the IT director. How can I find this out?
I got this from CPanel/WHM: (i do have root access)
Total server memory:
Memory: 74216004k/76546048k available (5468k kernel code, 1049156k absent, 1280888k reserved, 6953k data, 1316k init)
Current Memory Usage
total used free shared buffers cached
Mem: 74374696 73947984 426712 2460 296944 40927296
-/+ buffers/cache: 32723744 41650952
Swap: 15999996 316024 15683972
Total: 90374692 74264008 16110684
> Is there swapping?
I'm not the IT director. How can I find this out?
> If these are innodb tables,
product_info = innodb
vendor_base_costs = innodb
contract_price_13 = innodb
dealer_special = innodb
multi_level_retail_price = MyISAM
> what is innodb_buffer_pool_size?
20 GiB
... Also, some more Info:
CENTOS 6.9 x86_64
Total processors: 12
(Intel(R) Xeon(R) CPU E5-2630 v3 @ 2.40GHz)
> Could we see the Explain result on the same query without the match() clauses?
Not sure if you just want to remove the match()'s in the WHERE clause or in the ORDER BY clause too, so I did both ways:
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
EXPLAIN RESULT:
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
~~~~~~~
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
EXPLAIN RESULT:
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