Databases Reference
In-Depth Information
This query returns only 200 rows, but it needs to read thousands of rows to build the
result set. An index can't reduce the number of rows examined for a query like this one.
Unfortunately, MySQL does not tell you how many of the rows it accessed were used
to build the result set; it tells you only the total number of rows it accessed. Many of
these rows could be eliminated by a
WHERE
clause and end up not contributing to the
result set. In the previous example, after removing the index on
sakila.film_actor
, the
query accessed every row in the table and the
WHERE
clause discarded all but 10 of them.
Only the remaining 10 rows were used to build the result set. Understanding how many
rows the server accesses and how many it really uses requires reasoning about the query.
If you find that a huge number of rows were examined to produce relatively few rows
in the result, you can try some more sophisticated fixes:
• Use covering indexes, which store data so that the storage engine doesn't have to
retrieve the complete rows. (We discussed these in the previous chapter.)
• Change the schema. An example is using summary tables (discussed in
Chapter 4
).
• Rewrite a complicated query so the MySQL optimizer is able to execute it opti-
mally. (We discuss this later in this chapter.)
Ways to Restructure Queries
As you optimize problematic queries, your goal should be to find alternative ways to
get the result you want—but that doesn't necessarily mean getting the same result set
back from MySQL. You can sometimes transform queries into equivalent forms that
return the same results, and get better performance. However, you should also think
about rewriting the query to retrieve
different
results, if that provides an efficiency ben-
efit. You might be able to ultimately do the same work by changing the application
code as well as the query. In this section, we explain techniques that can help you
restructure a wide range of queries and show you when to use each technique.
Complex Queries Versus Many Queries
One important query design question is whether it's preferable to break up a complex
query into several simpler queries. The traditional approach to database design em-
phasizes doing as much work as possible with as few queries as possible. This approach
was historically better because of the cost of network communication and the overhead
of the query parsing and optimization stages.
However, this advice doesn't apply as much to MySQL, because it was designed to
handle connecting and disconnecting very efficiently and to respond to small and sim-
ple queries very quickly. Modern networks are also significantly faster than they used
to be, reducing network latency. Depending on the server version, MySQL can run well
over 100,000 simple queries per second on commodity server hardware and over 2,000
