Databases Reference
In-Depth Information
CHAPTER 6
Query Performance Optimization
In the previous chapters we explained schema optimization and indexing, which are
necessary for high performance. But they aren't enough—you also need to design your
queries well. If your queries are bad, even the best-designed schema and indexes will
not perform well.
Query optimization, index optimization, and schema optimization go hand in hand.
As you gain experience writing queries in MySQL, you will learn how to design tables
and indexes to support efficient queries. Similarly, what you learn about optimal
schema design will influence the kinds of queries you write. This process takes time,
so we encourage you to refer back to these three chapters as you learn more.
This chapter begins with general query design considerations—the things you should
consider first when a query isn't performing well. We then dig much deeper into query
optimization and server internals. We show you how to find out how MySQL executes
a particular query, and you'll learn how to change the query execution plan. Finally,
we'll look at some places MySQL doesn't optimize queries well and explore query
optimization patterns that help MySQL execute queries more efficiently.
Our goal is to help you understand deeply how MySQL really executes queries, so you
can reason about what is efficient or inefficient, exploit MySQL's strengths, and avoid
its weaknesses.
Why Are Queries Slow?
Before trying to write fast queries, remember that it's all about response time. Queries
are tasks, but they are composed of subtasks, and those subtasks consume time. To
optimize a query, you must optimize its subtasks by eliminating them, making them
happen fewer times, or making them happen more quickly.
1
1. Sometimes you might also need to modify a query to reduce its impact on other queries running on the
system. In this case, you're trying to reduce the query's resource consumption, a topic we discussed in
Chapter 3
.
