Databases Reference
In-Depth Information
optimizer_switch
This variable contains a set of flags that enable or disable specific optimizer fea-
tures. For example, in MySQL 5.1 you can use it to disable the index merge query
plan.
The first two options control optimizer shortcuts. These shortcuts are valuable for good
performance on complex queries, but they can cause the server to miss optimal plans
for the sake of efficiency. That's why it sometimes makes sense to change them.
Validating MySQL Upgrades
Trying to outsmart the MySQL optimizer usually is not a good idea. It generally creates
more work and increases maintenance costs for very little benefit. This is especially
relevant when you upgrade MySQL, because optimizer hints used in your queries might
prevent new optimizer strategies from being used.
In MySQL 5.0 a number of capabilities were added to the optimizer, and the as-yet
unreleased MySQL 5.6 will have the biggest changes to the optimizer in a very long
time. If you are upgrading to one of these versions, you will not want to miss out on
the benefits they offer.
New versions of MySQL generally improve the server by leaps and bounds, and this is
especially true in the 5.5 and 5.6 versions. MySQL upgrades usually go fine, but you
still need to test changes carefully. There is always a chance that you will discover an
edge case that affects you. The good news is that it's really easy to prevent this with a
little change management. Use the
pt-upgrade
tool from Percona Toolkit to validate
that your queries run well on the new version of MySQL, and that they don't return
different results.
Optimizing Specific Types of Queries
In this section, we give advice on how to optimize certain kinds of queries. We've
covered most of these topics in detail elsewhere in the book, but we wanted to make a
list of common optimization problems that you can refer to easily.
Most of the advice in this section is version-dependent, and it might not hold for future
versions of MySQL. There's no reason why the server won't be able to do some or all
of these optimizations itself someday.
Optimizing COUNT() Queries
The
COUNT()
aggregate function, and how to optimize queries that use it, is probably
one of the top 10 most-misunderstood topics in MySQL. You can do a web search and
find more misinformation on this topic than we care to think about.
Before we get into optimization, it's important that you understand what
COUNT()
really does.
