mysql， find_in_set 查询速度相当慢 ，in()又数据不全？有没有更合适的办法

Question

情况如下：  
 select id from table1 where find_in_set(type_id,getCatalogList(10004));
 select id from table1 where type_id in (type_id,getCatalogList(10004));需要找到table1中类别在getCatalogList(10004)类别中的数据  做统计及导出Excel  但是执行速度是在太慢了，有没有更好的办法  或方式 提高速度 注释：
getCatalogList(10004)是获取一个大类的子类的function

Answer 1

getCatalogList(10004) 的结果是什么？ 如果是个存储函数，那代码是什么？

Answer 2

存储过程 如下： where  find_in_set (p.catalog_id,product_line_child_list(catalogID) ) 这是主要调用的地方BEGINDECLARE catalogID  VARCHAR(10000);#100004DECLARE storageID  VARCHAR(100);#100000DECLARE startDatre  VARCHAR(30);#'2011-09-13 0:00:00' DECLARE endDatre  VARCHAR(30);#' '2011-09-18 23:59:59'DECLARE dateStr  VARCHAR(10000);#' '2011-09-18 23:59:59'SET  catalogID=catalogID_;SET  storageID=storageID_;SET  startDatre=startDatre_;SET  endDatre=endDatre_;CREATE TEMPORARY table IF NOT EXISTS temp_plan_and_stock
 select p.catalog_id ,c.title as catalog_title  ,p.plan_storage_id, s.title as storage_title, left(p.delivery_date,10) as delivery_date , 
 sum(p.quantity) as quantity  from plan_storage_sales p,product_catalog c,product_storage_catalog s 
 where  find_in_set (p.catalog_id,product_line_child_list(catalogID) )   and  p.catalog_id = c.id and p.plan_storage_id = s.id
 and p.plan_storage_id=storageID and  p.delivery_date between startDatre and endDatre
 group by p.catalog_id, left(p.delivery_date,10);set @sqlselect = dateStr_;
prepare stmtselect from @sqlselect;
execute stmtselect;
deallocate prepare stmtselect; 
END

Answer 3

这是function： getCatalogList(10004)  这是结果：100004,100004,100019,100020,100021,100022,101081,101136,100031,100032,100033,100034,100035,100036,100037,100038,100039,100040,100041,100042,100043,100044,100045,100046,100047,100048,100049,100062,100063,100064,100065,100068,100069,100070,100071,100072,100073,100074,100075,100076,100077,100078,100079,100080,100081,100082,100083,100084,100087,100092,100093,100095,100096,100097,100098,100099,100100,100101,100102,100103,100104,100105,100106,100107,100108,100109,100111,100112,100113,100114,100115,100116,100117,100118,100119,100120,100121,100122,100123,100124,100125,100127,100128,100129,100186,101084,101085,101091,101092,101093,101101,101103,101109,101110,101111,101112,101113,101114,101115,101116,101117,101119,101120,101121,101122,101123,101124,101125,101126,101127,101128,101129,101130,101131,101174,101175,101176,101177,101178,101179,101180,101181,101182,101183,101184,101185,101186,101187,101188,101189BEGIN
 
DECLARE sTemp VARCHAR(1000);DECLARE sTempChd VARCHAR(1000);DECLARE THE_CNT VARCHAR(1000);set sTemp =  SUBSTRING_INDEX(NodeId, ',', 1);set sTempChd=NodeId;WHILE sTempChd is not null DO         SET sTemp = concat(sTemp,',',sTempChd);         SELECT group_concat(id) INTO sTempChd FROM product_catalog where FIND_IN_SET(parentid,sTempChd)>0;    END WHILE;RETURN sTemp;END

Answer 4

where  find_in_set (p.catalog_id,getCatalogList(catalogID) )  调用这个才对 

Answer 5

直接测试，看效果如何，以断定是存储过程慢，还是FIND_IN_SET慢select id from table1 where find_in_set(type_id,'100004,100004,100019,100020,100021,100022,101081,101136,100031,100032,100033,100034,100035,100036,100037,100038,100039,100040,100041,100042,100043,100044,100045,100046,100047,100048,100049,100062,100063,100064,100065,100068,100069,100070,100071,100072,100073,100074,100075,100076,100077,100078,100079,100080,100081,100082,100083,100084,100087,100092,100093,100095,100096,100097,100098,100099,100100,100101,100102,100103,100104,100105,100106,100107,100108,100109,100111,100112,100113,100114,100115,100116,100117,100118,100119,100120,100121,100122,100123,100124,100125,100127,100128,100129,100186,101084,101085,101091,101092,101093,101101,101103,101109,101110,101111,101112,101113,101114,101115,101116,101117,101119,101120,101121,101122,101123,101124,101125,101126,101127,101128,101129,101130,101131,101174,101175,101176,101177,101178,101179,101180,101181,101182,101183,101184,101185,101186,101187,101188,101189');
另外建议还是用,如果ID上有索引
select id from table1 where type_id in  (100004,100004,100019,100020,100021,100022,101081,101136,100031,100032,100033,100034,100035,100036,100037,100038,100039,100040,100041,100042,100043,100044,100045,100046,100047,100048,100049,100062,100063,100064,100065,100068,100069,100070,100071,100072,100073,100074,100075,100076,100077,100078,100079,100080,100081,100082,100083,100084,100087,100092,100093,100095,100096,100097,100098,100099,100100,100101,100102,100103,100104,100105,100106,100107,100108,100109,100111,100112,100113,100114,100115,100116,100117,100118,100119,100120,100121,100122,100123,100124,100125,100127,100128,100129,100186,101084,101085,101091,101092,101093,101101,101103,101109,101110,101111,101112,101113,101114,101115,101116,101117,101119,101120,101121,101122,101123,101124,101125,101126,101127,101128,101129,101130,101131,101174,101175,101176,101177,101178,101179,101180,101181,101182,101183,101184,101185,101186,101187,101188,101189);

Answer 6

需求是：按大类统计，已知一个集合，在遍历这个集合是否是某大类别下的子类 是就统计，getCatalogList（）函数是获取大类下的小类，find_in_set()是看是否属于某大类，
set @sqlselect = dateStr_;
prepare stmtselect from @sqlselect;
execute stmtselect;
deallocate prepare stmtselect;  预处理是为了转化临时表里（即第一次查询的结果）的格式 。问题：find_in_set()速度很慢
    where p.catalog_id in (p.catalog_id,getCatalogList(catalogID) )用in总是把集合中所有数据都查出来 

Answer 7

测过了 是find_in_set 慢

Answer 8

先按 #8楼 的方法做测试。看结果再进行分析。
where id in (id) 类似于 where id=id 当然所有记录都会被选中。

Answer 9

where p.catalog_id in  (product_line_child_list(100004) ,100004) ；
或
where p.catalog_id in  (100004,product_line_child_list(100004) ) ；都是没结果的  这个in（）后面的第一个参数怎么设置

Answer 10

直接用这个
select id from table1 where type_id in  (100004,100004,100019,100020,100021,100022,101081,101136,100031,100032,100033,100034,100035,100036,100037,100038,100039,100040,100041,100042,100043,100044,100045,100046,100047,100048,100049,100062,100063,100064,100065,100068,100069,100070,100071,100072,100073,100074,100075,100076,100077,100078,100079,100080,100081,100082,100083,100084,100087,100092,100093,100095,100096,100097,100098,100099,100100,100101,100102,100103,100104,100105,100106,100107,100108,100109,100111,100112,100113,100114,100115,100116,100117,100118,100119,100120,100121,100122,100123,100124,100125,100127,100128,100129,100186,101084,101085,101091,101092,101093,101101,101103,101109,101110,101111,101112,101113,101114,101115,101116,101117,101119,101120,101121,101122,101123,101124,101125,101126,101127,101128,101129,101130,101131,101174,101175,101176,101177,101178,101179,101180,101181,101182,101183,101184,101185,101186,101187,101188,101189);

Answer 11

如果把(100004,100004,100019,100020,10002）直接放到in后面就可以 用product_line_child_list（100004）就不行了，这是为啥

Answer 12

这个可以 很快很正确 但是一把in后面 换成function(product_line_child_list(100004) ,100004)就废了

Answer 13

product_line_child_list(100004) 你返回的是个字符串，类似于  id in ("1,2,3,4") 当然不会有符合条件的。改你的程序，程序中先取得product_line_child_list(100004)的值，然后再用代码生成 select id from table1 where type_id in  (100004,100004,100019,100020,100021,100022,101081,101136,100031,100032,100033,100034,100035,100036,100037,100038,100039,100040,10004的语句进行执行。

Answer 14

这个可以直接在存储过程里改吧  还要去改java代码么   那不是要把这个存储过程拆开了

Answer 15

或者把sql拼接好 作为存储过程的参数传进来 那就还需要个预处理  弱弱的问一下 这里为了执行拼接传进来的sql用预处理 合适么

Answer 16

可以用PREPARE处理。但个人建议还是在JAVA程序中直接改。 或者在存储过程中直接生成需要的结果集。

Answer 17

java中改代码 有思路  ，或者在存储过程中直接生成需要的结果集。没有思路 把两个查询放在一起么  第二个查询是根据查询的两个日期 ，通过java代码获得日期之间的每一天 拼接成sql  把前一结果集的数据按小类别横向显示每一天的数据

Answer 18

product_line_child_list(catalogID) 这个结果集能直接转换成没有“”的么

Answer 19

你返回的是多个数字和逗号组成的一个字符串，无法转为数字。 既然你已经把数字写入到了一个临时表，为什么不直接与这个临时表做JOIN查询？然后返回需要的结果集？

Answer 20

zero_and_zero (zero_and_zero)
  '截至2011-10-27 17:36:46  用户结帖率0.00%  当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
http://topic.csdn.net/u/20100428/09/BC9E0908-F250-42A6-8765-B50A82FE186A.html
http://topic.csdn.net/u/20100626/09/f35a4763-4b59-49c3-8061-d48fdbc29561.html8、如何给分和结贴？
http://community.csdn.net/Help/HelpCenter.htm#结帖