Databases Reference
In-Depth Information
What are the subtasks that MySQL performs to execute a query, and which ones are
slow? The full list is impossible to include here, but if you profile a query as we showed
in
Chapter 3
, you will find out what tasks it performs. In general, you can think of a
query's lifetime by mentally following the query through its sequence diagram from the
client to the server, where it is parsed, planned, and executed, and then back again to
the client. Execution is one of the most important stages in a query's lifetime. It involves
lots of calls to the storage engine to retrieve rows, as well as post-retrieval operations
such as grouping and sorting.
While accomplishing all these tasks, the query spends time on the network, in the CPU,
in operations such as statistics and planning, locking (mutex waits), and most espe-
cially, calls to the storage engine to retrieve rows. These calls consume time in memory
operations, CPU operations, and especially I/O operations if the data isn't in memory.
Depending on the storage engine, a lot of context switching and/or system calls might
also be involved.
In every case, excessive time may be consumed because the operations are performed
needlessly, performed too many times, or are too slow. The goal of optimization is to
avoid that, by eliminating or reducing operations, or making them faster.
Again, this isn't a complete or accurate picture of a query's life. Our goal here is to show
the importance of understanding a query's lifecycle and thinking in terms of where the
time is consumed. With that in mind, let's see how to optimize queries.
Slow Query Basics: Optimize Data Access
The most basic reason a query doesn't perform well is because it's working with too
much data. Some queries just have to sift through a lot of data and can't be helped.
That's unusual, though; most bad queries can be changed to access less data. We've
found it useful to analyze a poorly performing query in two steps:
1. Find out whether your
application
is retrieving more data than you need. That
usually means it's accessing too many rows, but it might also be accessing too many
columns.
2. Find out whether the
MySQL server
is analyzing more rows than it needs.
Are You Asking the Database for Data You Don't Need?
Some queries ask for more data than they need and then throw some of it away. This
demands extra work of the MySQL server, adds network overhead,
2
and consumes
memory and CPU resources on the application server.
Here are a few typical mistakes:
2. Network overhead is worst if the application is on a different host from the server, but transferring data
between MySQL and the application isn't free even if they're on the same server.
