MySQL Stored Procedure Programming

Name

PER-07: Optimize conditional structures

Synopsis

The performance of IF and CASE statements is highly dependent on the number of comparisons that the statement must execute. The number of comparisons can be optimized in two ways:

• By placing the comparisons that are most frequently true earliest in the set of comparisons, you reduce the number of comparisons that must be executed.

• If any comparison is repeated in multiple expressions within the CASE or IF statement, you can extract that comparison and “nest” multiple CASE or IF statements. The inner comparisons need only be executed when the outer comparison evaluates to TRUE.