mysql存储过程优化

Question

小白，写了个存储过程，麻烦各位大佬该看一下，能不能优化下，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

Answer 1

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

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

 

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