MySQL Stored Procedure Programming

Chapter 20. Basic SQL Tuning

In this chapter, we will tune simple SQL statements that may be included in MySQL stored programs. In particular, we’ll optimize two of the most often executed SQL operations: retrieving data from a single table and joining two or more tables. Topics include:

• How to determine when the use of an index is required to optimize a query

• How to construct the best indexes to support specific queries

• How MySQL chooses between available indexes, and how to direct MySQL to use a specific index if necessary

• How to avoid “suppressing” an index

• What to do when no index will suffice to optimize a query

• How MySQL processes joins between multiple tables

• How to create indexes that optimize table joins

• How to determine the optimal join order and how to force MySQL to use a particular join order

Chapter 21 builds on these fundamentals, optimizing more complex SQL operations.

Examples in this chapter are based on tables created using the InnoDB storage engine. Although the same MySQL optimizer is used for all storage engines, you may observe different behaviors in other storage engines because of differences in optimizer statistics and indexing approaches.