O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required