2
\$\begingroup\$

I've tried to improve the speed of my query in MySQL, but when condition IN clause happens too often, the query runs very slowly. Can anyone help me optimize the query?

EXPLAIN SELECT bi.syouhin_sys_code AS syouhin_sys_code     
    ,dwh_syo.name AS syouhin_sys_name     
    ,dwh_syo.code AS syouhin_code     
    ,dwh_syo.brand_code AS brand_code     
    ,brand.name AS brand_name     
    ,bi.bunrui_code AS bunrui_code     
    ,bunrui.bunrui_name AS bunrui_name     
    ,SUM(bi.syouhin_count) AS syouhin_count     
    ,CASE WHEN dwh_syo.teika = 0       
       THEN 0 ELSE IFNULL(TRUNCATE((sik.price / (dwh_syo.teika * 100 / 105)) * 100, 2), 0)      
     END AS siire_rate       
    ,SUM(bi.jyutyuu_teika) AS jyutyuu_teika     
    ,SUM(bi.jyutyuu_hanbai) AS jyutyuu_hanbai     
    ,SUM(bi.jyutyuu_sikiri) AS jyutyuu_sikiri     
    ,SUM(bi.jyutyuu_hanbai) - SUM(bi.jyutyuu_sikiri) AS jyutyuu_profit     
    ,CASE WHEN SUM(bi.jyutyuu_hanbai) = 0       
        THEN 0 ELSE TRUNCATE(((SUM(bi.jyutyuu_hanbai) - SUM(bi.jyutyuu_sikiri)) / SUM(bi.jyutyuu_hanbai) * 100),2)      
     END  AS jyutyuu_profit_rate         
    ,SUM(bi.nouhin_teika) AS nouhin_teika     
    ,SUM(bi.nouhin_hanbai) AS nouhin_hanbai     
    ,SUM(bi.nouhin_sikiri) AS nouhin_sikiri     
    ,SUM(bi.nouhin_hanbai) - SUM(bi.nouhin_sikiri) AS nouhin_profit     
    ,CASE WHEN SUM(bi.nouhin_hanbai) = 0       
        THEN 0 ELSE TRUNCATE(((SUM(bi.nouhin_hanbai) - SUM(bi.nouhin_sikiri)) / SUM(bi.nouhin_hanbai) * 100),2)      
     END AS nouhin_profit_rate     
    ,CONCAT(CASE WHEN sel.hyouji_name1 IS NULL OR sel.hyouji_name1 = '' 
                THEN '' ELSE sel.hyouji_name1 
             END         
            ,CASE WHEN sel.hyouji_name2 IS NULL OR sel.hyouji_name2 = '' 
                THEN '' ELSE CONCAT('/',sel.hyouji_name2) 
             END         
            ,CASE WHEN sel.hyouji_name3 IS NULL OR sel.hyouji_name3 = '' 
                THEN '' ELSE CONCAT('/',sel.hyouji_name3) 
             END         
            ,CASE WHEN sel.hyouji_name4 IS NULL OR sel.hyouji_name4 = '' 
                THEN '' ELSE CONCAT('/',sel.hyouji_name4) 
             END            
            ,CASE WHEN sel.hyouji_name5 IS NULL OR sel.hyouji_name5 = '' 
                THEN '' ELSE CONCAT('/',sel.hyouji_name5) 
             END            
            ,CASE WHEN sel.hyouji_name6 IS NULL OR sel.hyouji_name6 = '' 
                THEN '' ELSE CONCAT('/',sel.hyouji_name6) 
             END) AS syouhin_spec  
FROM bi_bunrui_syouhin_shop_day_jyutyuu AS bi           
    LEFT OUTER JOIN dwh_rc_syouhin AS dwh_syo 
        ON bi.syouhin_sys_code = dwh_syo.syouhin_sys_code      
    LEFT OUTER JOIN tbl_sikiri_now AS sik 
        ON bi.syouhin_sys_code = sik.syouhin_sys_code      
    LEFT OUTER JOIN mst_syouhin_all_select AS sel 
        ON bi.syouhin_sys_code = sel.syouhin_sys_code      
    LEFT OUTER JOIN mst_brand AS brand 
        ON dwh_syo.brand_code = brand.brand_code      
    LEFT OUTER JOIN mst_bunrui AS bunrui 
        ON bi.bunrui_code = bunrui.bunrui_code  
WHERE bi.tyumon_date_unixtime BETWEEN UNIX_TIMESTAMP(20121001) 
    AND UNIX_TIMESTAMP(20130603) 
    AND( bi.syouhin_sys_code LIKE '%%' OR     dwh_syo.name  LIKE '%%' ) 
    AND bi.shop_code IN ('0000','0001','0002','0009','0003','0006','9000','9001') 
    AND bi.bunrui_code IN ('0001','1000','1001','1005','1006','1007','1008','1009'
        ,'1010','1011','1012','1013','1014','1015','1016','1017',
        '1018','1020','1021','1022','1023','1030','1031','1032'
        ,'1033','1034','1036','1037','1038','1039','1040','1041'
        ,'1043','1044','1045','1046','1050','1054','1055','1060'
        ,'1061','1070','1080','1081','1082','1083','1084','1085','1086'
        ,'1088','1090','1091','1092','1093','1094','1095','1096','1097'
        ,'1100','1101','1102','1103','1104','1105','1106','1107','1108'
        ,'1109','1110','1111','1112','1113','1114','1115','1116','1117'
        ,'1118','1119','1120','1121','1122','1123','1130','1131','1132'
        ,'1133','1134','1135','1136','1137','1140','1141','1142','1143'
        ,'1145','1148','1150','1151','1152','1153','1154','1155','1156'
        ,'1157','1160','1161','1162','1163','1164','1165','1166','1167'
        ,'1168','1169','1170','1171','1180','1181','1182','1183','1184'
        ,'1187','1189','1200','1201','1202','1203','1204','1205','1206'
        ,'1207','1210','1211','1212','1220','2003','2009','2022','2028'
        ,'2042','2048','2062','2068','2082','2088','2102','2108','2122'
        ,'2125','2128','2129','2134','2141','2144','2161','2182','2186'
        ,'2222','2228','2242','2253','2262','2268','2302','2511','2512'
        ,'2513','3180','3207','3208','4023','4024','4028','4029','4030'
        ,'4031','4032','4033','4034','4036','4040','4041','4042','4043'
        ,'4044','4062','4064','4066','4067','4068','4069','4070','4071'
        ,'4092','5013','5014','5015','5016','5017','5018','6200','6201'
        ,'6202','6203','6204','6205','6220','6221','6222','6223','6224'
        ,'6225','6226','6234','6235','6240','6241','6242','6243','6244'
        ,'6245','6246','6247','6249','6250','6251','6260','6261','6262'
        ,'6263','6264','6265','6280','6281','6282','6283','6284','6285'
        ,'6286','6287','6288','6289','6290','6291','6292','6293','6300'
        ,'6301','6302','6303','6304','6305','6320','6321','6322','6323'
        ,'6324','6325','6340','6342','6343','6344','6345','6346','6347'
        ,'6348','6349','6360','6361','6362','6363','6364','6365','6366'
        ,'6367','6368','6369','6371','6372','6373','6374','6375','6376'
        ,'6377','6378','6379','6380','6381','6382','6383','6384','6385'
        ,'6400','6401','6402','6403','6406','6407','6408','6420','6421'
        ,'6422','6423','6424','6425','6426','6427','6428','6429','6430'
        ,'6431','6432','6433','6434','6435','6436','6440','6441','6442'
        ,'6443','6444','6445','6446','6447','6448','6449','6450','6451'
        ,'6452','6453','6454','6455','6456','6457','6458','6459','6460'
        ,'6470','6471','6472','6473','6474','6475','6476','6477','6478'
        ,'6480','6481','6482','6483','6484','6485','6486','6487','6489'
        ,'6490','6500','6501','6502','6504','6505','6506','6507','6508'
        ,'6509','6510','6511','6512','6513','6514','6515','6520','6521'
        ,'6522','6523','6524','6525','6526','6527','6528','6529','6530'
        ,'6531','6550','6551','6552','6553','6554','6555','6556','6557'
        ,'6558','6559','6560','6561','6562','6563','6564','6565','6567'
        ,'6568','6569','6570','6572','6573','6574','7694','7993','9200'
        ,'9201','9202','9203','9204','9205','9206','9208','9310','9320')  
GROUP BY bi.syouhin_sys_code  
HAVING SUM(bi.syouhin_count) > 0  
ORDER BY jyutyuu_hanbai DESC

This is the statement that makes the query run slowly:

AND bi.bunrui_code IN ('0001','1000','1001',...)

Here is the EXPLAIN output:

id select_type table   type   possible_keys                                 key     key_len ref                         rows   Extra
1  SIMPLE      bi      range  PRIMARY                                       PRIMARY 12      NULL                        256360 Using where; Using temporary; Using filesort
1  SIMPLE      dwh_syo eq_ref PRIMARY                                       PRIMARY 8       analyst.bi.syouhin_sys_code 1      Using where
1  SIMPLE      sik     eq_ref PRIMARY,syouhin_sys_code,idx_syouhin_sys_code PRIMARY 8       analyst.bi.syouhin_sys_code 1                       
1  SIMPLE      sel     eq_ref PRIMARY                                       PRIMARY 8       analyst.bi.syouhin_sys_code 1                       
1  SIMPLE      brand   eq_ref PRIMARY                                       PRIMARY 4       analyst.dwh_syo.brand_code  1                       
1  SIMPLE      bunrui  eq_ref PRIMARY                                       PRIMARY 8       analyst.bi.bunrui_code      1

The run time is more than 16s.

\$\endgroup\$
4
  • 1
    \$\begingroup\$ Welcome to Code Review. This is a very specific question with little information about your own research into the problem, and as such is likely to be closed. Could you please add more information, for example the actual EXPLAIN output, the alternatives you've tried, and their timings? \$\endgroup\$
    – l0b0
    Commented Jun 4, 2013 at 13:36
  • \$\begingroup\$ @l0b0 I added more information \$\endgroup\$
    – user25815
    Commented Jun 5, 2013 at 1:55
  • 2
    \$\begingroup\$ Difficult to comment without knowing the table layouts, as I can't really see what the indexes are that it has chosen to use. However with close to 500 items in the IN clause it might well be better to instead put those items into a temp table and then join that temp table against bi_bunrui_syouhin_shop_day_jyutyuu . \$\endgroup\$
    – Kickstart
    Commented Jun 7, 2013 at 8:17
  • 2
    \$\begingroup\$ how do you know that it isn't this statement AND( bi.syouhin_sys_code LIKE '%%' OR dwh_syo.name LIKE '%%' ) \$\endgroup\$
    – Malachi
    Commented Nov 29, 2013 at 2:58

2 Answers 2

Reset to default
6
\$\begingroup\$

Optimizing this query can only properly be done on a system where the data exists, and you can 'play' a little bit. There are four things I recommend that you improve:

  1. temporary table
  2. better case statements
  3. better joins
  4. LIKE

Temp table:

create temporary table validbunrui (bunrui_code int not null, bunrui_name nvarchar(40) not null);

insert into validbunrui
select bunrui_code, bunrui_name
from bunrui
where bunrui_code IN ('0001','1000','1001','1005','1006','1007','1008','1009'
    ,'1010','1011','1012','1013','1014','1015','1016','1017',
    '1018','1020','1021','1022','1023','1030','1031','1032'
    ,'1033','1034','1036','1037','1038','1039','1040','1041'
    ,'1043','1044','1045','1046','1050','1054','1055','1060'
    ,'1061','1070','1080','1081','1082','1083','1084','1085','1086'
    ,'1088','1090','1091','1092','1093','1094','1095','1096','1097'
    ,'1100','1101','1102','1103','1104','1105','1106','1107','1108'
    ,'1109','1110','1111','1112','1113','1114','1115','1116','1117'
    ,'1118','1119','1120','1121','1122','1123','1130','1131','1132'
    ,'1133','1134','1135','1136','1137','1140','1141','1142','1143'
    ,'1145','1148','1150','1151','1152','1153','1154','1155','1156'
    ,'1157','1160','1161','1162','1163','1164','1165','1166','1167'
    ,'1168','1169','1170','1171','1180','1181','1182','1183','1184'
    ,'1187','1189','1200','1201','1202','1203','1204','1205','1206'
    ,'1207','1210','1211','1212','1220','2003','2009','2022','2028'
    ,'2042','2048','2062','2068','2082','2088','2102','2108','2122'
    ,'2125','2128','2129','2134','2141','2144','2161','2182','2186'
    ,'2222','2228','2242','2253','2262','2268','2302','2511','2512'
    ,'2513','3180','3207','3208','4023','4024','4028','4029','4030'
    ,'4031','4032','4033','4034','4036','4040','4041','4042','4043'
    ,'4044','4062','4064','4066','4067','4068','4069','4070','4071'
    ,'4092','5013','5014','5015','5016','5017','5018','6200','6201'
    ,'6202','6203','6204','6205','6220','6221','6222','6223','6224'
    ,'6225','6226','6234','6235','6240','6241','6242','6243','6244'
    ,'6245','6246','6247','6249','6250','6251','6260','6261','6262'
    ,'6263','6264','6265','6280','6281','6282','6283','6284','6285'
    ,'6286','6287','6288','6289','6290','6291','6292','6293','6300'
    ,'6301','6302','6303','6304','6305','6320','6321','6322','6323'
    ,'6324','6325','6340','6342','6343','6344','6345','6346','6347'
    ,'6348','6349','6360','6361','6362','6363','6364','6365','6366'
    ,'6367','6368','6369','6371','6372','6373','6374','6375','6376'
    ,'6377','6378','6379','6380','6381','6382','6383','6384','6385'
    ,'6400','6401','6402','6403','6406','6407','6408','6420','6421'
    ,'6422','6423','6424','6425','6426','6427','6428','6429','6430'
    ,'6431','6432','6433','6434','6435','6436','6440','6441','6442'
    ,'6443','6444','6445','6446','6447','6448','6449','6450','6451'
    ,'6452','6453','6454','6455','6456','6457','6458','6459','6460'
    ,'6470','6471','6472','6473','6474','6475','6476','6477','6478'
    ,'6480','6481','6482','6483','6484','6485','6486','6487','6489'
    ,'6490','6500','6501','6502','6504','6505','6506','6507','6508'
    ,'6509','6510','6511','6512','6513','6514','6515','6520','6521'
    ,'6522','6523','6524','6525','6526','6527','6528','6529','6530'
    ,'6531','6550','6551','6552','6553','6554','6555','6556','6557'
    ,'6558','6559','6560','6561','6562','6563','6564','6565','6567'
    ,'6568','6569','6570','6572','6573','6574','7694','7993','9200'
    ,'9201','9202','9203','9204','9205','9206','9208','9310','9320')

Then, use this table in the join with the main table instead of referencing bunrui directly.

select .....
       .....
       ,bunrui.bunrui_code AS bunrui_code     
       ,bunrui.bunrui_name AS bunrui_name
       ,.....
       ,.....
from ......
     ......      
LEFT OUTER JOIN validbunrui AS bunrui 
    ON bi.bunrui_code = bunrui.bunrui_code

Better CASE statements.

Your code has a lot of case statements:

,CONCAT(CASE WHEN sel.hyouji_name1 IS NULL OR sel.hyouji_name1 = '' 
            THEN '' ELSE sel.hyouji_name1 
         END         
        ,CASE WHEN sel.hyouji_name2 IS NULL OR sel.hyouji_name2 = '' 
            THEN '' ELSE CONCAT('/',sel.hyouji_name2) 
         END         
        ,CASE WHEN sel.hyouji_name3 IS NULL OR sel.hyouji_name3 = '' 
            THEN '' ELSE CONCAT('/',sel.hyouji_name3) 
         END         
        ,CASE WHEN sel.hyouji_name4 IS NULL OR sel.hyouji_name4 = '' 
            THEN '' ELSE CONCAT('/',sel.hyouji_name4) 
         END            
        ,CASE WHEN sel.hyouji_name5 IS NULL OR sel.hyouji_name5 = '' 
            THEN '' ELSE CONCAT('/',sel.hyouji_name5) 
         END            
        ,CASE WHEN sel.hyouji_name6 IS NULL OR sel.hyouji_name6 = '' 
            THEN '' ELSE CONCAT('/',sel.hyouji_name6) 
         END) AS syouhin_spec

These case statements can be replaced with MySQL's IFNULL(..., ...) function:

    ,CONCAT(IFNULL(sel.hyouji_name1, ''),
            IFNULL(sel.hyouji_name2 ....
            ....) AS syouhin_spec

Better joins.

I really struggle to believe that you need all of those outer joins.

Outer joins are a really, really poor performance choice. Are you really sure that those all need to be outer joins? Only you can answer that, but, you should investigate.

LIKE clauses.

LIKE clauses are also really slow. They require a scan of the data rather than an index search.... Further, your LIKE queries are bogus ....:

AND( bi.syouhin_sys_code LIKE '%%' OR     dwh_syo.name  LIKE '%%' )

What is that supposed to do? Whatever it is, you have a problem.... ;-)

\$\endgroup\$
3
\$\begingroup\$

Make a temporary table with bunrui_code and bunrui_name as the other poster suggests, put an index on the code and then inner left join it (not outer left join it) with the main query, that way you can simply remove the IN statement in the where clause.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.