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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

migrated from Feb 5 '13 at 17:01

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

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

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

Your Answer


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