3 ms·
You know, if I had access to the documentation of a major DB system, I would put something about this right on the documentation page for LIMIT and OFFSET. It's
by jerf 9y ago
You know, if I had access to the documentation of a major DB system, I would put something about this right on the documentation page for LIMIT and OFFSET. It's just such an attractive nuisance; who can blame a novice developer/DB user for making the mistake of thinking that LIMIT and OFFSET are a good solution, really?
- i_s 9y ago> who can blame a novice Even experts fall into this trap, actually: https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch https://sqlperformance.com/2015/01/t-sql-queries/pagination-...
- shub 9y agoLIMIT 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.
- barrkel 9y agoWell, if you need to support arbitrary sorting and filtering with your pagination, you'll be looking at table scans no matter what you do, so limit and offset don't really add much extra cost. You can arrange for indexes on the most commonly used columns, so that e.g. initial default page load is fast, while leaving the user hanging for up to a second or two on the more unusual queries. It turns out that when people have better filtering and sorting tools, they spent a lot less time digging for page 10,001.
- jlg23 9y ago> who can blame a novice developer/DB user for making the mistake of thinking that LIMIT and OFFSET are a good solution, really? Who else is to blame? Should every vendor turn the documentation into a textbook on relational databases and their intrinsic properties?
- nothrabannosir 9y agoGood documentation, useful to the humans reading it in actual, real life? Solving the actual problem they’re having and not just “technically correct” answering the exact question they asked? One can only hope.
- jerf 9y agoI'm asking for a note about an extremely common performance issue to be added to the documentation. Any database documentation worth it's salt is full of performance notes already. It hardly seems like there's a slippery slope down to... whatever it is you seem to fear here.