MySQL Internals Manual  /  Stored Programs  /  Flow Analysis Optimizations

16.5 Flow Analysis Optimizations

After code is generated, the low level sp_instr instructions are optimized. The optimization focuses on two areas:

  • Dead code removal,

  • Jump shortcut resolution.

These two optimizations are performed together, as they both are a problem involving flow analysis in the graph that represents the generated code.

The code that implements these optimizations is sp_head::optimize().


Do not confuse sp_head::optimize() with the component named the optimizer, as they are very different. The former is specific to Stored Programs, and focuses on improving the flow of statements, whereas the latter is general to queries, and focuses on finding the best execution plan when executing a single statement. For the optimizer, see Optimization.

The (Stored Program) optimizer is invoked from only one place, in the following code:

db_load_routine(..., sp_head **sphp, ...)

By disabling the call to sp_head::optimize() and recompiling the code, SHOW PROCEDURE CODE will display the code before flow optimization.


When investigating issues related to this area, you may want to use a DBUG_EXECUTE_IF to avoid recompiling the server with or without flow optimization every time. Be careful to shutdown and restart the server with or without the call to sp_head::optimize() for each test, or you will find that caching of a Stored Program code does interfere.

User Comments
Sign Up Login You must be logged in to post a comment.