A table with about 70K records is displayed on a site, showing 50 records per page.
Pagination is done with limit offset,50
on the query, and the records can be ordered on different columns.
Browsing the latest pages (so the offset is around 60,000) makes the queries much slower than when browsing the first pages (about 10x)
Is this an issue of using the limit
command?
Are there other ways to get the same results?
With large offsets, MySQL
needs to browse more records.
Even if the plan uses filesort
(which means that all records should be browsed), MySQL
optimizes it so that only $offset + $limit
top records are sorted, which makes it much more efficient for lower values of $offset
.
The typical solution is to index the columns you are ordering on, record the last value of the columns and reuse it in the subsequent queries, like this:
SELECT *
FROM mytable
ORDER BY
value, id
LIMIT 0, 10
which outputs:
value id
1 234
3 57
4 186
5 457
6 367
8 681
10 366
13 26
15 765
17 345 -- this is the last one
To get to the next page, you would use:
SELECT *
FROM mytable
WHERE (value, id) > (17, 345)
ORDER BY
value, id
LIMIT 0, 10
, which uses the index on (value, id)
.
Of course this won't help with arbitrary access pages, but helps with sequential browsing.
Also, MySQL
has certain issues with late row lookup. If the columns are indexed, it may be worth trying to rewrite your query like this:
SELECT *
FROM (
SELECT id
FROM mytable
ORDER BY
value, id
LIMIT $offset, $limit
) q
JOIN mytable m
ON m.id = q.id
See this article for more detailed explanations:
It's how MySQL deals with limits. If it can sort on an index (and the query is simple enough) it can stop searching after finding the first offset + limit
rows. So LIMIT 0,10
means that if the query is simple enough, it may only need to scan 10 rows. But LIMIT 1000,10
means that at minimum it needs to scan 1010 rows. Of course, the actual number of rows that need to be scanned depend on a host of other factors. But the point here is that the lower the limit + offset
, the lower that the lower-bound on the number of rows that need to be scanned is...
As for workarounds, I would optimize your queries so that the query itself without the LIMIT
clause is as efficient as possible. EXPLAIN
is you friend in this case...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With