I try to improve the speed of my query in mysql but when condition IN clause to much. the query run very slow. Anyone can help me to optimize the query here.
Thanks for any Help

    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

the In here make sql slow

    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')