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;
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.