MySQL Internals Manual  /  ...  /  Dead Code Removal

15.5.1 Dead Code Removal

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 reason: the 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 IF.

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 removed.

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.


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