High Performance MySQL

Chapter 5. Query Performance

This chapter deals with an issue faced by every MySQL user sooner or later: speeding up slow queries. MySQL is a very fast database server, but its innate speed can carry your applications only so far. Eventually you need to roll up your sleeves, get your hands dirty, and figure out why your queries are slow—and ultimately figure out what needs to be done to get a response quickly.

We’re frequently asked how we “figure this stuff out.” It’s really quite simple. Once you start to understand how MySQL does what it does, you’ll begin to have an intuitive feeling for it, and query optimization will start to seem really easy. It’s not always that easy, but with the proper background, you should end up able to figure out most optimization problems.

This chapter aims to provide a framework for understanding how MySQL works to resolve queries. With this foundation, you can continue through this chapter to the next, where the knowledge is applied to application design and server performance tuning.

We’ll begin with an overview of how MySQL handles query processing. After that, we’ll look at the optimizer’s built-in features. Then we’ll discuss identifying slow queries and finish up with a look at some of the hints you can provide to MySQL’s query optimizer.

Query Processing Basics

How MySQL goes from receiving a query to sending the results back to a client is relatively straightforward. The work happens in several distinct stages. Let’s walk through them.

Query ...