Databases Reference
In-Depth Information
If the table is very large, this query is better written as follows:
mysql>
SELECT film.film_id, film.description
->
FROM sakila.film
->
INNER JOIN (
->
SELECT film_id FROM sakila.film
->
ORDER BY title LIMIT 50, 5
->
) AS lim USING(film_id);
This “deferred join” works because it lets the server examine as little data as possible
in an index without accessing rows, and then, once the desired rows are found, join
them against the full table to retrieve the other columns from the row. A similar tech-
nique applies to joins with
LIMIT
clauses.
Sometimes you can also convert the limit to a positional query, which the server can
execute as an index range scan. For example, if you precalculate and index a position
column, you can rewrite the query as follows:
mysql>
SELECT film_id, description FROM sakila.film
->
WHERE position BETWEEN 50 AND 54 ORDER BY position;
Ranked data poses a similar problem, but usually mixes
GROUP BY
into the fray. You'll
almost certainly need to precompute and store ranks.
The problem with
LIMIT
and
OFFSET
is really the
OFFSET
, which represents rows the
server is generating and throwing away. If you use a sort of bookmark to remember the
position of the last row you fetched, you can generate the next set of rows by starting
from that position instead of using an
OFFSET
. For example, if you want to paginate
through rental records, starting from the newest rentals and working backward, you
can rely on the fact that their primary keys are always increasing. You can fetch the first
set of results like this:
mysql>
SELECT * FROM sakila.rental
->
ORDER BY rental_id DESC LIMIT 20;
This query returns rentals 16049 through 16030. The next query can continue from
that point:
mysql>
SELECT * FROM sakila.rental
->
WHERE rental_id < 16030
->
ORDER BY rental_id DESC LIMIT 20;
The nice thing about this technique is that it's very efficient no matter how far you
paginate into the table.
Other alternatives include using precomputed summaries, or joining against redundant
tables that contain only the primary key and the columns you need for the
ORDER BY
.
You can also use Sphinx; see
Appendix F
for more information.
