CODEWITHSUNDEEP
javamongodb
john cena
john cena

Reputation: 191

What is the best way for pagination on mongodb using java

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?

Upvotes: 4

Views: 15843

Answers (2)

Jdk12
Jdk12

Reputation: 1

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);

Upvotes: 0

yellowB
yellowB

Reputation: 3020

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().

Upvotes: 14

Related Questions