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() ?
Add a comment
|
3 Answers
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);
2 Comments
CuriousLearner
What does this do?
Niton
It does explains the querry inside the procedure if you call it
Prior to MySQL 5.7:
EXPLAIN worked only on SELECT statements, except when you use EXPLAIN tablename which is an alias of DESCRIBE tablename
after 5.7:
EXPLAIN works with SELECT DELETE INSERT REPLACE UPDATE statements
4 Comments
Xenos
Furicane
@Xenos did you... see the date of my answer?
Xenos
Yes, that's why it's worth a mention, so others readers are up-to-date and not sticking to old obsolete answers
New Alexandria
@Xenos in a case like this, it's better to edit the answer, as long as it's in line with the original answer, and is no longer a dupe. If it's a dupe-after-edit you can flag to remove to avoid confusion. If you didn't have the rep at the time to edit, then agree that commenting is a good way to help others until the edit is approved.