4 ms·
LIMIT is great, and a necessity on MySQL. It's OFFSET that causes problems. If I were king of databases I'd make OFFSET > 1000 cause an error directing the user
by shub 9y ago
LIMIT is great, and a necessity on MySQL. It's OFFSET that causes problems. If I were king of databases I'd make OFFSET > 1000 cause an error directing the user to the doc page explaining why what they're doing is a bad idea and how to increase the limit anyway.
- developer2 9y agoSome systems do. Elasticsearch is the example that comes to mind. By default, configuration prevents you from offsetting past I believe it's result 10,000. You can of course increase this limit, but there is a sensible default.
- barrkel 9y agoOffset 1000 won't hurt you if it's a simple indexed scan; MySQL will mess you over far worse if you use a subquery in your where clause, you can be looking at 1M+ rows scanned via the power of nested loops. I've invested several months of my life optimizing arbitrary sort and filter for MySQL results over tables varying from 100k rows to 100M (using time windows to scope things). As long as you can efficiently cut down the underlying data set into the region of 100k using some kind of window - usually recency based - and convince MySQL to filter by this before it sorts or does any other kind of filter - then limit / offset pagination doesn't hurt. You can then use higher-level pagination on the recency window if it becomes necessary.