using EXPLAIN for MYSQL stored procedure calls

Question

How do analyse and use EXPLAIN for my stored procedure calls ? I need to optimize the query time, however seems like there is no where i can do a EXPLAIN call proc_name() ?

Answer 1

You can try

set profiling=1;
call proc_name();
show profiles;

Answer 2

at present you can't explain stored procedures in mysql - but you could do something like this:

drop procedure if exists get_user;
delimiter #
create procedure get_user
(
in p_user_id int unsigned,
in p_explain tinyint unsigned
)
begin
  if (p_explain) then
    explain select * from users where user_id = p_user_id;
  end if;
  select * from users where user_id = p_user_id;
end#

delimiter ;

call get_user(1,1);