mysql存储过程优化

小白，写了个存储过程，麻烦各位大佬该看一下，能不能优化下，1000条数据，查询了两秒

CREATE PROCEDURE `getAllChildren`(pid bigint(20),typeCode varchar(20))
begin
declare last_count int;
declare last_level int;
set last_level = 0;
drop table if exists temp;
drop table if exists result;
create temporary table temp (id bigint(20) primary key, parent_id bigint(20),type varchar(20),name varchar(50), level int);
create temporary table result (id bigint(20) primary key, parent_id bigint(20), type varchar(20),name varchar(50), level int);
insert into temp(id, parent_id, type,name, level) select id, parent_res_id, res_type,res_name, last_level from pue_res_manage where parent_res_id = pid and delete_state = '0' ;
while exists (select * from temp) do
		insert into result select * from temp;
    delete from temp;
    insert into temp(id, parent_id,type,name, level) 
    select id, parent_res_id,res_type,res_name, last_level + 1
    from pue_res_manage 
    where parent_res_id in (select id from result where level = last_level) and delete_state = '0';
    set last_level = last_level + 1;
end while;
if typeCode is null then
	select id from result;
else
	select id from result where type  = typeCode;
end if;
end

看看字段能不能使用枚举?

然后创建表如果只是临时用的话，看能不能创建临时表

存储过程如果只是临时用的话可以。业务代码中不能用的呦

这个存储过程是用来查询树表，某个节点下的所有子节点的，之前有用过方法，但是查不全

CREATE FUNCTION `getAllChilds`(`pid` BIGINT) RETURNS varchar(4000) CHARSET utf8
    NO SQL
BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);
    
    SET sTemp='';
    SET sTempChd = CAST(pid AS CHAR);
    WHILE sTempChd IS NOT NULL DO
    SET sTemp= CONCAT(sTemp,',',sTempChd);
    SELECT GROUP_CONCAT(id) INTO sTempChd FROM pue_res_manage WHERE FIND_IN_SET(parent_res_id,sTempChd)>0 and delete_state = '0';
    END WHILE;
    #去除前面的逗号
    RETURN SUBSTRING(sTemp,2);
END;