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:
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:
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.
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:
|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.
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;
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
MySQL Built-in Functions
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development
MySQL Stored Procedure Programming
Similar book on Amazon