3 ms·
> “ Offset pagination is bad for performance. Avoid it at all costs unless you want some spider indexing page 10,432 of your archives to eat all your CPU.” I l
by retreatguru 7y ago
> “ Offset pagination is bad for performance. Avoid it at all costs unless you want some spider indexing page 10,432 of your archives to eat all your CPU.”
I love this! It’s so true only bots dive that deep and it’s usually a total waste. But what do you recommend instead? Should you just deny any calls past page 10? Or do away with pagination altogether?
- hombre_fatal 7y agoIf there's somehow no need to show anything past page 10, then by all means, just do that. But most applications can't get away with that. There are plenty of resources for pagination online. For example, your [Next Page] button could use a `where date > {date of last item on prev page}` since you have an index on date. e.g. <a href="/things?after={prevDate}">Next</a>. A forum topic could have a consecutive and contiguous "index" on each of its posts that starts at 0 and increments. Now going to page 1000 is simply a matter of grabbing posts between index 990 to 1000 which is instant.
- emmelaich 7y agohttps://use-the-index-luke.com/no-offset https://use-the-index-luke.com/no-offset
- samanator 7y agoThanks! This is really useful! I didn't know that limit offset scanned all rows before the offset.
- hilbertseries 7y agoDon’t use offset for paging. Presumably you’re sorting the results by something, add a condition on that value in the where clause based on the last row in the previous page.
- janpot 7y agoSuper fun until you have a column that has an amount of duplicates greater than the page size. Now you'll have to add a second column to your order clause, your serialized cursor and your where clause. Oh, and don't forget to check whether your database driver uses the same date precision as your database does, because you might be in for another surprise.