Databases Reference
In-Depth Information
More complex optimizations
In general,
COUNT()
queries are hard to optimize because they usually need to count a
lot of rows (i.e., access a lot of data). Your only other option for optimizing within
MySQL itself is to use a covering index. If that doesn't help enough, you need to
make changes to your application architecture. Consider summary tables (covered in
Chapter 4
), and possibly an external caching system such as
memcached
. You'll prob-
ably find yourself faced with the familiar dilemma, “fast, accurate, and simple: pick
any two.”
Optimizing JOIN Queries
This topic is actually spread throughout most of the topic, but we'll mention a few
highlights:
• Make sure there are indexes on the columns in the
ON
or
USING
clauses. Consider
the join order when adding indexes. If you're joining tables
A
and
B
on column
c
and the query optimizer decides to join the tables in the order
B
,
A
, you don't need
to index the column on table
B
. Unused indexes are extra overhead. In general, you
need to add indexes only on the second table in the join order, unless they're needed
for some other reason.
• Try to ensure that any
GROUP BY
or
ORDER BY
expression refers only to columns from
a single table, so MySQL can try to use an index for that operation.
• Be careful when upgrading MySQL, because the join syntax, operator precedence,
and other behaviors have changed at various times. What used to be a normal join
can sometimes become a cross product, a different kind of join that returns differ-
ent results, or even invalid syntax.
Optimizing Subqueries
The most important advice we can give on subqueries is that you should usually prefer
a join where possible, at least in current versions of MySQL. We covered this topic
extensively earlier in this chapter. However, “prefer a join” is not future-proof advice,
and if you're using MySQL 5.6 or newer versions, or MariaDB, subqueries are a whole
different matter.
Optimizing GROUP BY and DISTINCT
MySQL optimizes these two kinds of queries similarly in many cases, and in fact con-
verts between them as needed internally during the optimization process. Both types
of queries benefit from indexes, as usual, and that's the single most important way to
optimize them.
MySQL has two kinds of
GROUP BY
strategies when it can't use an index: it can use a
temporary table or a filesort to perform the grouping. Either one can be more efficient
