Using MySQL EXPLAIN to analyze the performance of a function

Question

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.

Answer 1

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.

Answer 2

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.