MySQL Internals Manual  /  ...  /  Jump Shortcut Resolution

16.5.2 Jump Shortcut Resolution

The term jump shortcut refers to the following optimization: when instruction A is a jump (conditional or not) that goes to position B, and when B is an unconditional jump to position C, the code can be changed so that A can jump to C directly, taking a shortcut to avoid the unnecessary B. Consider the following SQL code:

CREATE PROCEDURE proc_6(x int, y int, z int)
BEGIN
  SELECT "Start";

  IF (x > 0)
  THEN
    BEGIN
      SELECT "x looks ok";
      IF (y > 0)
      THEN
        BEGIN
          SELECT "so does y";
          IF (z > 0)
          THEN
            SELECT "even z is fine";
          ELSE
            SELECT "bad z";
          END IF;
        END;
      ELSE
        SELECT "bad y";
      END IF;
    END;
  ELSE
    SELECT "bad x";
  END IF;

  SELECT "Finish";
END$$

Before flow optimization, the compiled code is:

SHOW PROCEDURE CODE proc_6;
Pos     Instruction
0       stmt 0 "SELECT "Start""
1       jump_if_not 12(13) (x@0 > 0)
2       stmt 0 "SELECT "x looks ok""
3       jump_if_not 10(11) (y@1 > 0)
4       stmt 0 "SELECT "so does y""
5       jump_if_not 8(9) (z@2 > 0)
6       stmt 0 "SELECT "even z is fine""
7       jump 9
8       stmt 0 "SELECT "bad z""
9       jump 11
10      stmt 0 "SELECT "bad y""
11      jump 13
12      stmt 0 "SELECT "bad x""
13      stmt 0 "SELECT "Finish""

Note the jump 9 at position 7: since the instruction at position 9 is jump 11, the code at position 7 can be simplified to jump 11. The optimization is also recursive: since the instruction 11 is jump 13, the final jump destination for the instruction at position 7 is jump 13. Conditional jumps are optimized also, so that the instruction 5: jump_if_not 8(9) can be optimized to jump_if_not 8(13).

After flow optimization, the compiled code is:

SHOW PROCEDURE CODE proc_6;
Pos     Instruction
0       stmt 0 "SELECT "Start""
1       jump_if_not 12(13) (x@0 > 0)
2       stmt 0 "SELECT "x looks ok""
3       jump_if_not 10(13) (y@1 > 0)
4       stmt 0 "SELECT "so does y""
5       jump_if_not 8(13) (z@2 > 0)
6       stmt 0 "SELECT "even z is fine""
7       jump 13
8       stmt 0 "SELECT "bad z""
9       jump 13
10      stmt 0 "SELECT "bad y""
11      jump 13
12      stmt 0 "SELECT "bad x""
13      stmt 0 "SELECT "Finish""

Note the differences with every jump instruction.

Caution

For clarity, this example has been designed to not involve dead code. Note that in general, an instruction that was reachable before taking a shortcut might become unreachable after the shortcut, so that the optimizations for jump shortcuts and dead code are tightly intertwined.


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