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