Why Dead Code Drastically Slows MySQL Functions, Procedures, and Triggers

Analyzing MySQL Stored Procedure Functions

MySQL developers often use stored procedures, functions, and triggers, but dead code—code that never executes—can severely degrade performance. Four simple functions were created in MySQL 5.7 to illustrate this effect.

Function 1 declares a variable and returns it (a no‑op function).

Function 2 calls another function but contains an IF 1=2 branch that never runs.

Function 3 has four conditions, all of which are impossible, so the code inside never executes.

Function 4 mirrors Function 3 with a select does_not_exist that is never reached.

All functions ultimately return 0, yet their execution times differ dramatically when run 1 million times:

Function 1 completes in 1.75 seconds, Function 2 (with one dead‑code branch) in 2.45 seconds, and Function 3 (four dead branches) takes nearly three times longer than Function 1. Function 3_nope, which removes the dead code, matches Function 1.

Visualizing System Calls

To see what happens during execution, the performance_schema /sys schema and the ps_trace_thread() procedure were used. The steps are:

Obtain the thread ID of the MySQL connection.

In another session, run ps_trace_thread(thread_id).

Switch back to the original connection and execute the target function.

After ten seconds, the trace data is collected and plotted.

Graphs for each function (Func1, Func2, Func3) show that every IF check generates a sp / jump_if_not call followed by an open‑table operation, confirming that parsing dead‑code conditions adds overhead.

How Dead Code Affects Trigger Performance

Even when a trigger does not execute any meaningful logic, dead code inside the called functions still impacts batch updates. A trigger that calls func1() (a no‑op) adds measurable latency. Adding a second virtual trigger roughly doubles the overhead.

Replacing the called function with func3(), which contains dead code but otherwise behaves like func1(), demonstrates that the dead code alone slows the trigger execution.

Memory Allocation

MySQL must parse and allocate memory for the entire stored routine or trigger body on each execution, even if parts of the code never run. This can lead to memory bloat and, in some cases, memory leaks as reported in MySQL Bug #86821.

Conclusion

Parsing of stored procedures and trigger events occurs at execution time, and dead code—no matter how unreachable—significantly degrades performance, especially in bulk operations or when invoked from triggers. Simply disabling a trigger with a flag does not eliminate this cost; the dead code must be removed.

References:

MySQL Bug #91585 – https://bugs.mysql.com/bug.php?id=91585

MySQL Bug #86821 – https://bugs.mysql.com/bug.php?id=86821