Query Performance Optimization - High Performance MySQL - page 246

optimizer decides to use a different strategy. Most such queries we see are accidents
(because the server doesn't complain), or are the result of laziness rather than being
designed that way for optimization purposes. It's better to be explicit. In fact, we suggest
that you set the server's SQL_MODE configuration variable to include ONLY_FULL
_GROUP_BY so it produces an error instead of letting you write a bad query.
MySQL automatically orders grouped queries by the columns in the GROUP BY clause,
unless you specify an ORDER BY clause explicitly. If you don't care about the order and
you see this causing a filesort, you can use ORDER BY NULL to skip the automatic sort.
You can also add an optional DESC or ASC keyword right after the GROUP BY clause to
order the results in the desired direction by the clause's columns.
Optimizing GROUP BY WITH ROLLUP
A variation on grouped queries is to ask MySQL to do superaggregation within the
results. You can do this with a WITH ROLLUP clause, but it might not be as well optimized
as you need. Check the execution method with EXPLAIN , paying attention to whether
the grouping is done via filesort or temporary table; try removing the WITH ROLLUP and
seeing if you get the same group method. You might be able to force the grouping
method with the hints we mentioned earlier in this section.
Sometimes it's more efficient to do superaggregation in your application, even if it
means fetching many more rows from the server. You can also nest a subquery in the
FROM clause or use a temporary table to hold intermediate results, and then query the
temporary table with a UNION .
The best approach might be to move the WITH ROLLUP functionality into your
application code.
Optimizing LIMIT and OFFSET
Queries with LIMIT s and OFFSET s are common in systems that do pagination, nearly
always in conjunction with an ORDER BY clause. It's helpful to have an index that sup-
ports the ordering; otherwise, the server has to do a lot of filesorts.
A frequent problem is having a high value for the offset. If your query looks like LIMIT
10000, 20 , it is generating 10,020 rows and throwing away the first 10,000 of them,
which is very expensive. Assuming all pages are accessed with equal frequency, such
queries scan half the table on average. To optimize them, you can either limit how many
pages are permitted in a pagination view, or try to make the high offsets more efficient.
One simple technique to improve efficiency is to do the offset on a covering index,
rather than the full rows. You can then join the result to the full row and retrieve the
additional columns you need. This can be much more efficient. Consider the following
query:
mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;