How to "LIMIT 50000,1"?

Question

So, LIMIT 50000,1 won't work well? Then, how come? What to do if rows after several rows are required?

I read it at http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

Answer 1

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)

Answer 2

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)