Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

What is the cost difference between paging with a cursor or using offset?

When creating a results page with [Next Page] and [Prev Page] buttons, what is the cost difference between using a cursor to do this or using offset? And what are the pros and cons of each technique?

As a concrete example, what is the cost of reading results 100-110.

I have seen claims that offset uses "small datastore operations" and some that claim it uses a full "read operation" for each entity skipped.

Using cursors, I have read that they cannot page backwards, but I noticed a new Cursor.reverse() method for the first time today.

I assume that the disadvantages of using a cursor are that you cannot jump to a page by number e.g. straight to results 90-100.

like image 757
Doug Avatar asked Apr 24 '12 03:04

Doug


1 Answers

Skipping results costs you a datastore small operation per skipped result. It's also slower than using cursors.

As you observe, reverse cursors are now available, which will allow you to page backwards, as long as the appropriate indexes for your query exist.

You can, of course, combine both cursors and offsets, if you want to skip to page 'n'.

like image 64
Nick Johnson Avatar answered Oct 17 '22 18:10

Nick Johnson