Dead code is also known as unreachable code: code that cannot possibly be executed, because no path in the logic leads to it.
For example, consider the following SQL code:
CREATE PROCEDURE proc_5() BEGIN DECLARE i INT DEFAULT 0; again: WHILE TRUE DO BEGIN set i:= i+1; SELECT "This code is alive"; IF (i = 100) THEN LEAVE again; END IF; ITERATE again; SELECT "This code is dead"; END; END WHILE; END$$
Before flow optimization, the compiled code is:
SHOW PROCEDURE CODE proc_5; Pos Instruction 0 set i@0 0 1 jump_if_not 10(10) 1 2 set i@0 (i@0 + 1) 3 stmt 0 "SELECT "This code is alive"" 4 jump_if_not 7(7) (i@0 = 100) 5 jump 10 6 jump 7 7 jump 1 8 stmt 0 "SELECT "This code is dead"" 9 jump 1
Note the instruction at position 8: the previous instruction is an unconditional jump, so the flow of control can never reach 8 by coming from 7. Because there exists no jump in the entire code that leads to 8 either, the instruction at 8 is unreachable. By looking further in the flow, because 8 is unreachable and there are no jumps to position 9, the instruction at position 9 is also unreachable.
The instruction at position 6 is also unreachable, for a similar
THEN part of the if contains a
jump, due to the statement
LEAVE again;, so
that the code never executes the jump generated by the compiler
to go from the end of the
THEN block to the
statement following the
After detecting all the unreachable instructions, and simplifying the code, the result after flow optimization is:
SHOW PROCEDURE CODE proc_5; Pos Instruction 0 set i@0 0 1 jump_if_not 10(10) 1 2 set i@0 (i@0 + 1) 3 stmt 0 "SELECT "This code is alive"" 4 jump_if_not 1(1) (i@0 = 100) 5 jump 10
The flow optimizer is good at detecting
most of the dead code, but has limitations.
For example, coding in SQL
IF FALSE THEN ... END
IF; leads to code that can never be executed, but
since the flow optimizer does neither propagate constants nor
consider impossible conditional jumps, this code will not be
The goal of the flow optimizer is mostly to perform simple local optimizations with a low cost. It's not a fully featured code optimizer, and does not guard against poor SQL.