Performance
Most of the best practices outlined so far concentrate on the maintainability and correctness of our stored programs. The following practices concentrate on the performance of stored programs.
| PER-01: Concentrate on tuning SQL to improve stored program performance |
There are many ways to improve stored program performance, but none of these are likely to have much effect if the SQL within the stored program is inefficient.
Most stored programs contain SQL, and for almost all of those stored programs, the SQL makes up the vast majority of stored program elapsed time. Attempts to tune the stored program by other means (loop tuning, for instance) should only be attempted once the SQL in the stored program has been tuned.
| PER-02: Carefully create the best set of indexes for your application |
The primary purpose of indexes is to allow MySQL to rapidly retrieve the information you need. Just as the index in this book allows you to find some information without having to read the entire book, an index allows MySQL to get rows from the table without reading the entire table.
Determining the optimal set of indexes for your application is, therefore, probably the single most important step you can take to optimize MySQL stored program performance. In general, you should create indexes that support WHERE clause conditions and joins. You should also create multicolumn (concatenated) indexes, so that a single index can support all of the columns in the WHERE clause or all of the columns required to join two tables.
You should create indexes to support joins, since without an appropriate index, joins will degrade rapidly as the row counts in the involved tables increase.
| PER-03: Avoid accidental table scans |
One of the most common causes of poor application performance is the "accidental" full table scan. An accidental table scan occurs when the nature of the query, or the expectations of the programmer, suggests that the query will be satisfied using an index, but instead a full table scan is performed.
Accidental table scans can occur under the following circumstances:
- The index that you believe supports the query does not exist.
- You have an index that includes the columns in the query, but you don include the foremost, "leading" columns in your query.
- You suppress an index by enclosing the column concerned with a function or an expression.
- You specify a nonleading substring as the search condition. For instance, you try to find all employees whose name ends in "STONE" (WHERE name LIKE \%STONE).
Most accidental table scans can be resolved by creating a new index or rewording the SQL so that the index is not suppressed. See Chapters 20 and 21 for more details.
| PER-04: Optimize necessary table scans |
Using an index to retrieve rows from a table is worthwhile only when you are retrieving a relatively small subset of rows from the table. Over a certain proportion of the table (say 5-20%), it is more efficient to read every row from the table. However, it is still possible to optimize these "necessary" table scans. For instance:
- You can move long, infrequently accessed columns to a secondary table.
- You can create an index on all of the columns required for the query. MySQL can then scan the entire index to resolve the query. Since the index will normally be smaller than the table, it ought to be quicker to scan the index.
These techniques are discussed in detail in Chapter 21.
| PER-05: Avoid using stored programs for computationally expensive routines |
Like most stored program implementations, MySQL stored programs are optimized for database access, not computational speed. If you have a choice, place your most computationally expensive routines in client or middle-tier code. For instance, you might want to implement your most expensive calculations in PHP or Java rather than in stored programs.
| PER-06: Move loop invariant expressions outside of loops |
Whenever you set out to tune your stored programs (having completed your SQL optimization), you should first take a look at your loops. Any inefficiency inside a loops body will be magnified by the multiple executions of that code.
A common mistake is to put execute code within the body of a loop that has the same result with each iteration of the loop. When you identify such a scenario, extract the static code, assign the outcomes of that code to one or more variables in advance of the loop, and then reference those variables inside the loop.
Example
At first glance, this loop block seems sensible enough, but in reality it is quite inefficient:
WHILE (i<=1000) do SET j=1; WHILE (j<=1000) do SET counter=counter+1; SET sumroot=sumroot+sqrt(i)+sqrt(j); SET j=j+1; END WHILE; SET i=i+1; END WHILE;
This code contains two loops: we calculate the square root of i inside of the inner loop, even though it only changes for each iteration of the outer loop. Consequently, we calculate the square root 1,000,000 times, even though we have only 1,000 distinct values.
Heres the optimized version of that same code:
WHILE (i<=@i) do SET rooti=sqrt(i); SET counter=counter+1; SET j=1; WHILE (j<=@j) do SET sumroot=sumroot+rooti+sqrt(j); SET j=j+1; END WHILE; SET i=i+1; END WHILE;
A small change, but one that will have a massive effect on performance.
| PER-07: Optimize conditional structures |
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.
| PER-08: Structure IF and CASE statements so more likely expressions appear earliest in the list |
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 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;
