MySQL Forums
Forum List  »  Optimizer & Parser

Re: Can anyone help speed up this query?
Posted by: Search Team
Date: June 22, 2017 09:15AM

> 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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Can anyone help speed up this query?
573
June 22, 2017 09:15AM


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.