MySQL Stored Procedure Programming

Name

PER-08: Structure IF and CASE statements so more likely expressions appear earliest in the list

Synopsis

When MySQL processes a CASE or an IF statement, it works through every ELSEIF or WHEN condition in the statement until if finds a condition that returns TRUE. If you place the condition that is most likely to evaluate to TRUE at the beginning of your conditional statement, you will improve the overall efficiency of your program.

Your primary concern, however, should be the readability of your IF and CASE statement. Don't worry about reorganizing the clauses of your IF and CASE statements unless you have identified them as a bottleneck in application performance.

Example

In this example the most likely condition is tested last:

    IF (percentage>95) THEN
        SET Above95=Above95+1;
    ELSEIF (percentage >=90) THEN
        SET Range90to95=Range90to95+1;
    ELSEIF (percentage >=75) THEN
        SET Range75to89=Range75to89+1;
    ELSE
        SET LessThan75=LessThan75+1;
    END IF;

To optimize the statement, we can reword it so that in most cases, only one comparison is necessary:

    IF (percentage<75) THEN
        SET LessThan75=LessThan75+1;
    ELSEIF (percentage >=75 AND percentage<90) THEN
        SET Range75to89=Range75to89+1;
    ELSEIF (percentage >=90 and percentage <=95) THEN
        SET Range90to95=Range90to95+1;
    ELSE
        SET Above95=Above95+1;
    END IF;