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 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
The run time is more than 16s.
