Speed up MySQL query with IN clause

Question

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.

• l0b0: 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?
• user25815: @l0b0 I added more information
• Kickstart: 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 .
• Malachi: how do you know that it isn't this statement AND( bi.syouhin_sys_code LIKE '%%' OR dwh_syo.name LIKE '%%' )

Answer 1

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.... ;-)

Answer 2

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.