Tuning Stored Programs and Their SQL

This chapter kicks off the set of chapters in this book that are concerned with optimizing the performance of your stored programs. Like any program, a stored program might be correct in all of its functional aspects, but still be considered a failure if it does not perform well. Performance tuning of MySQL stored programs is of particular importance because the stored program language is interpreted, and thus it does not benefit from the performance improvements that can be obtained by optimizing compilers such as the ones common in languages such as C and Java. (Strictly speaking, Java is also an interpreted language, but the Java JVM performs a number of sophisticated optimizations.) Stored programs also almost always involve significant database activity and therefore are quite likely to become a performance bottleneck for the application as a whole.

We believe that there are three main principles of stored program optimization:

Optimize SQL

The SQL inside of a stored program must be optimized if the stored program has any chance of running efficiently. Untuned SQL statements can easily take hundreds or even thousands of times longer to return results than well-tuned SQL statements, so we therefore recommend tuning the SQL inside a stored program before tuning the stored program code itself. We'll look at SQL tuning in detail in the next few chapters.

Break up complex SQL

Sometimes you can use stored programs to break up complex and hard-to-tune SQL statements into distinct, smaller statements that are easier to tune individuallyboth for the MySQL optimizer (the part of MySQL that determines how SQL should be executed) and for the programmer who is trying to tune the SQL. We'll look at these cases in Chapter 22.

Perform non-SQL optimization

Finally, optimizations that are common and well known in other programming languages also apply to the MySQL stored program language. Loop structures, use of recursion, caching, and branching structures can all affect how fast the SQL will run. We'll examine how to optimize the non-SQL stored program code in detail in Chapter 22.

In this chapter, we provide a brief overview of the way in which MySQL processes SQL statements, review the tuning tools at our disposal, and provide a brief overview of tuning. In subsequent chapters we will delve more deeply into the tuning of stored programs and the SQL statements they contain.