LIMIT 50000,1 won’t work well? Then, how come? What to do if rows after several rows are required?
If you specify a
LIMIT, MySQL will process the query without the limit, and then just read ahead until it gets to the limit point.
In other words, if you specify
LIMIT 50000,1, the database will have to read 50001 records just to get the one you’re interested in.
If the limit is low, this doesn’t really matter, but with a high limit, it’ll add a significant amount of time to the query.
All of this is covered in the link you provided (which seems to be quite a good resource).
If you’re doing this kind of thing and need performance over a large number of pages, you may be better off doing a more direct query, for example querying where the sort column is greater than the last entry on the previous page. There are pitfalls to this approach as well, of course, but the one thing it will be is quicker than
LIMIT 50000,1 (as long as you have an index, of course)
It says nowhere that it wont work, only that performance may suffer. As you can see, the writer suggests that you may remember the last id and do a
where id > last_id limit 20 instead of just
limit 5000, 20.
Beware of large LIMIT Using index to
sort is efficient if you need first
few rows, even if some extra filtering
takes place so you need to scan more
rows by index then requested by LIMIT.
However if you’re dealing with LIMIT
query with large offset efficiency
will suffer. LIMIT 1000,10 is likely
to be way slower than LIMIT 0,10. It
is true most users will not go further
than 10 page in results, however
Search Engine Bots may very well do
so. I’ve seen bots looking at 200+
page in my projects. Also for many web
sites failing to take care of this
provides very easy task to launch a
DOS attack – request page with some
large number from few connections and
it is enough. If you do not do
anything else make sure you block
requests with too large page numbers.
For some cases, for example if results
are static it may make sense to
precompute results so you can query
them for positions. So instead of
query with LIMIT 1000,10 you will have
WHERE position between 1000 and 1009
which has same efficiency for any
position (as long as it is indexed)