Skip to main content
Code Review

Return to Question

reformatted EXPLAIN output
Source Link
200_success
200_success
  • 144.2k
  • 22
  • 188
  • 473

Here is the EXPLAIN resultsoutput:

id  select_type table   type    possible_keys                                 key     key_len ref                         rows   Extra
1   SIMPLE      bi  range    range  PRIMARY                                       PRIMARY 12      NULL    256360                    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    eq_ref PRIMARY,syouhin_sys_code,idx_syouhin_sys_code   PRIMARY 8       analyst.bi.syouhin_sys_code 1                       
1   SIMPLE      sel eq_ref    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

Here is the EXPLAIN results:

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

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
added 1068 characters in body
Source Link
Malachi
Malachi
  • 28.7k
  • 11
  • 86
  • 188
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

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

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 isn't the output of the query, it is result of EXPLAIN function
Source Link
Malachi
Malachi
  • 28.7k
  • 11
  • 86
  • 188

The IN hereThis is the statement that makes the SQLquery run slowly:

Here is the outputEXPLAIN results:

The IN here makes the SQL run slowly:

Here is the output:

This is the statement that makes the query run slowly:

Here is the EXPLAIN results:

deleted 14 characters in body; edited title
Source Link
Jamal
Jamal
  • 34.9k
  • 13
  • 133
  • 237
deleted 2901 characters in body; edited tags
Source Link
Malachi
Malachi
  • 28.7k
  • 11
  • 86
  • 188
added 600 characters in body
Source Link
user25815
user25815
  • 83
  • 3
added 2991 characters in body
Source Link
user25815
user25815
  • 83
  • 3
Source Link
user25815
user25815
  • 83
  • 3