4

I am trying to create a simple pagination in mongodb by below code.

collection.find().skip(n).limit(n);

but doesn't it looks like there will be a performance issue if we see that in java terms first find will return all the records consider i have 2 million records, then it will pass it to skip method then it will be passed to limit method. it means every time this query will be fetching all the db records or mongodb drivers work differently, what i missed?

john cena
  • 191
  • 1
  • 1
  • 7

2 Answers2

14

When talking about pagination in MongoDB, it is easily to write this code:

collection.find().skip(pageSize*(pageNum-1)).limit(pageSize);

Above is the native solution supported by MongoDB, but this is not efficient if there are huge documents in the collection. Suppose you have 100M documents, and you want to get the data from the middle offset(50Mth). MongoDB has to build up the full dataset and walk from the beginning to the specified offset, this will be low performance. As your offset increases, the performance keeps degrade.

The root cause is the skip() command which is not efficient and can not take big benifit from index.

Below is another solution to improve performance on large data pagination:

The typical usage scenario of pagination is that there is a table or list to show data of specified page, and also a 'Previous Page' & 'Next Page' button to load data of previous or next page.

If you got the '_id' of the last document in current page, you can use find() instead of skip(). Use _id > currentPage_LastDocument._id as one of the criteria to find next page data. Here is pseudocode:

//Page 1
collection.find().limit(pageSize);
//Get the _id of the last document in this page
last_id = ...

//Page 2
users = collection.find({'_id': {$gt: last_id}}).limit(pageSize);
//Update the last id with the _id of the last document in this page
last_id = ...

This will avoid MongoDB to walk through large data when using skip().

SQB
  • 3,926
  • 2
  • 28
  • 49
yellowB
  • 2,900
  • 1
  • 16
  • 19
  • 1
    how is that possible with second approach that i show page numbers like 1, 2,3,4,5 and user can easily move between them. – john cena May 11 '18 at 20:05
  • @johncena, system design depends on the usage & scenario of your system, but you don't talk about anything about it. If you want to show page numbers for user to click, I think you can only show (current - 2, current - 1, current, current + 1, current + 2) instead of all page numbers, then you can take benifit from approach-2. Actually I guess user doesn't want to see page numbers from 1~9999 all in the page :) – yellowB May 14 '18 at 01:40
  • What if I want to press "double triangle button" to get 10+ pages(which is a common UI req)? And directly to the last page? I think there are no better way than skip/limit in this situation. – WesternGun Aug 03 '20 at 14:17
  • Actually with 1M records, I see one `find(Criteria).skip().limit().sort()` and one `find(Criteria).count()` will return me correct total number and pagination in 5s(1st query)/3s(2nd query) with arbitrary page number. Not very very good, but no other choices. – WesternGun Aug 03 '20 at 14:19
  • this solution will prevent us from sorting the collection, is there a way to use this technique while still being able to sort other columns/fields? – Miko Chu Aug 05 '21 at 05:39
0

Another approach can be

  1. save document _id as int, not ObjectId.
{_id : 1, title : ''}  //first document
{_id : 2, title : ''}  //second document
  1. use the last document _id to query next page.
collection.find({ _id: {$gt: last_id} }).limit(10);
  1. use int to query specific pages.
//find document 11 to 20
collection.find({ _id: {$gt: 10} }).limit(10);

//find document 21 to 30
collection.find({ _id: {$gt: 20} }).limit(10);
Jdk12
  • 1