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.