Databases Reference
In-Depth Information
Is MySQL Examining Too Much Data?
Once you're sure your queries
retrieve
only the data you need, you can look for queries
that
examine
too much data while generating results. In MySQL, the simplest query
cost metrics are:
• Response time
• Number of rows examined
• Number of rows returned
None of these metrics is a perfect way to measure query cost, but they reflect roughly
how much data MySQL must access internally to execute a query and translate ap-
proximately into how fast the query runs. All three metrics are logged in the slow query
log, so looking at the slow query log is one of the best ways to find queries that examine
too much data.
Response time
Beware of taking query response time at face value. Hey, isn't that the opposite of what
we've been telling you? Not really. It's still true that response time is what matters, but
it's a bit complicated.
Response time is the sum of two things: service time and queue time.
Service time
is
how long it takes the server to actually process the query.
Queue time
is the portion of
response time during which the server isn't really executing the query—it's waiting for
something, such as waiting for an I/O operation to complete, waiting for a row lock,
and so forth. The problem is, you can't break the response time down into these com-
ponents unless you can measure them individually, which is usually hard to do. In
general, the most common and important waits you'll encounter are I/O and lock waits,
but you shouldn't count on that, because it varies a lot.
As a result, response time is not consistent under varying load conditions. Other
factors—such as storage engine locks (table locks and row locks), high concurrency,
and hardware—can also have a considerable impact on response times. Response time
can also be both a symptom and a cause of problems, and it's not always obvious which
is the case, unless you can use the techniques shown in
“Single-Query Versus Server-
Wide Problems” on page 93
to find out.
When you look at a query's response time, you should ask yourself whether the re-
sponse time is reasonable for the query. We don't have space for a detailed explanation
in this topic, but you can actually calculate a quick upper-bound estimate (QUBE) of
query response time using the techniques explained in Tapio Lahdenmaki and Mike
Leach's book
Relational Database Index Design and the Optimizers
(Wiley). In a nut-
shell: examine the query execution plan and the indexes involved, determine how many
sequential and random I/O operations might be required, and multiply these by the
