Query Performance Optimization - High Performance MySQL - page 248

Optimizing SQL_CALC_FOUND_ROWS
Another common technique for paginated displays is to add the SQL_CALC_FOUND_ROWS
hint to a query with a LIMIT , so you'll know how many rows would have been returned
without the LIMIT . It might seem that there's some kind of “magic” happening here,
whereby the server predicts how many rows it would have found. But unfortunately,
the server doesn't really do that; it can't count rows it doesn't actually find. This option
just tells the server to generate and throw away the rest of the result set, instead of
stopping when it reaches the desired number of rows. That's very expensive.
A better design is to convert the pager to a “next” link. Assuming there are 20 results
per page, the query should then use a LIMIT of 21 rows and display only 20. If the 21st
row exists in the results, there's a next page, and you can render the “next” link.
Another possibility is to fetch and cache many more rows than you need—say, 1,000—
and then retrieve them from the cache for successive pages. This strategy lets your
application know how large the full result set is. If it's fewer than 1,000 rows, the
application knows how many page links to render; if it's more, the application can just
display “more than 1,000 results found.” Both strategies are much more efficient than
repeatedly generating an entire result and discarding most of it.
Sometimes you can also just estimate the full size of the result set by running an
EXPLAIN query and looking at the rows column in the result (hey, even Google doesn't
show exact result counts!). If you can't use these tactics, using a separate COUNT(*) query
to find the number of rows can be much faster than SQL_CALC_FOUND_ROWS , if it can use
a covering index.
Optimizing UNION
MySQL always executes UNION queries by creating a temporary table and filling it with
the UNION results. MySQL can't apply as many optimizations to UNION queries as you
might be used to. You might have to help the optimizer by manually “pushing down”
WHERE , LIMIT , ORDER BY , and other conditions (i.e., copying them, as appropriate, from
the outer query into each SELECT in the UNION ).
It's important to always use UNION ALL , unless you need the server to eliminate
duplicate rows. If you omit the ALL keyword, MySQL adds the distinct option to the
temporary table, which uses the full row to determine uniqueness. This is quite ex-
pensive. Be aware that the ALL keyword doesn't eliminate the temporary table, though.
MySQL always places results into a temporary table and then reads them out again,
even when it's not really necessary (for example, when the results could be returned
directly to the client).