Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

You can precede a SELECT statement in MySQL with EXPLAIN, but how would you explain a function? I have a function that does a lot of work and I would like to analyze the performance of the function as a whole.

share|improve this question
add comment

migrated from serverfault.com Feb 5 '13 at 17:01

This question came from our site for professional system and network administrators.

2 Answers

It depends on what you want to do.

If you want an execution plan, you'll have to EXPLAIN each of the queries inside the function individually. MySQL doesn't have a feature that will do this for you.

If you just want to measure the execution time but the function runs too fast to meaningfully measure then you can use the BENCHMARK() function to repeatedly call the function. Then you can make changes to the function and run BENCHMARK() again and compare the results.

share|improve this answer
add comment

EXPLAIN was meant to do queries, not functions or procedures.

When it comes to procedural code, you must think like a developer all over again. For what purposes would you optimize code, especially for a database ?

  • Streamline Code Execution
    • Use SQL for all rows
    • Forcing SQL behavior over the Query Optimizer
  • Reduce Client/Server Traffic
    • Minimize Responses
    • Triggers
  • Divide/Conquer Complex Operations
    • Optimize Correlated Queries
    • Avoid Self Joins
    • Remove Unnecessary Statements from Loops
    • Use Controlled Break Statements (LEAVE or CONTINUE)
    • Good IF/CASE statements
    • Recursion
    • Cursors

It is no longer a case of EXPLAIN a query. If you can EXPLAIN the procedure, you can code. Obviously, if you can EXPLAIN a procedure faster, you can produce faster code.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.