MongoDB - paging

2 months ago

Using skip+limit is not a good way to do paging when performance is an issue, or with large collections; it will get slower and slower as you increase the page number. Using skip requires the server to walk though all the documents (or index values) from 0 to the offset (skip) value.

It is much better to use a range query (+ limit) where you pass in the last page's range value. For example if you are sorting by "publishdate" you would simple pass the last "publishdate" value as the criteria for the query to get the next page of data.

 

The cursor.skip() method is often expensive because it requires the server to walk from the beginning of the collection or index to get the offset or skip position before beginning to return result. As offset (e.g. pageNumber above) increases, cursor.skip() will become slower and more CPU intensive. With larger collections, cursor.skip() may become IO bound.

 

To achieve pagination in a scaleable way combine a limit( ) along with at least one filter criterion, a createdOn date suits many purposes.

MyModel.find( { createdOn: { $lte: request.createdOnBefore } } )
.limit( 10 )
.sort( '-createdOn' )

My database

> db.users.find({},{name:1, date_join:1}).sort({_id:1}).pretty()
{ "_id" : 1, "name" : "Robin", "date_join" : 1500620170958 }
{ "_id" : 2, "name" : "Le On", "date_join" : 1501057294658 }
{ "_id" : 3, "name" : "Choi Song", "date_join" : 1501057370519 }
{ "_id" : 4, "name" : "Tiet Tran", "date_join" : 1501057403122 }
{ "_id" : 5, "name" : "Tran Vuong", "date_join" : 1501057420789 }
{ "_id" : 6, "name" : "Jet Le", "date_join" : 1501057443631 }
{ "_id" : 7, "name" : "Tao Ten Hoa1", "date_join" : 1501057539265 }
{ "_id" : 8, "name" : "Harry Tran", "date_join" : 1501057559332 }
{ "_id" : 9, "name" : "Linh Tran", "date_join" : 1501057579787 }
{ "_id" : 10, "name" : "Cu Teo", "date_join" : 1501057620762 }
{ "_id" : 11, "name" : "Truong Giang", "date_join" : 1501057648154 }
{ "_id" : 13, "name" : "Hoa Hong 2", "date_join" : 1500340554720 }
{ "_id" : 14, "name" : "Hanah", "date_join" : 1500340747429 }
{ "_id" : 15, "name" : "Queeny", "date_join" : 1500341591283 }
{ "_id" : 16, "name" : "Daisy", "date_join" : 1500341659779 }
{ "_id" : 17, "name" : "Jesly", "date_join" : 1500341688791 }
{ "_id" : 18, "name" : "Kitty", "date_join" : 1500345106003 }
{ "_id" : 19, "name" : "Monkey", "date_join" : 1500348180899 }
{ "_id" : 20, "name" : "Sexy A", "date_join" : 1500436249968 }
{ "_id" : 21, "name" : "Rose", "date_join" : 1500436392888 }

Sort 

db.users.find({date_join:{$gt:1501057403122}},{name:1, date_join:1}).pretty().sort({_id:1}).limit(4)
{ "_id" : 5, "name" : "Tran Vuong", "date_join" : 1501057420789 }
{ "_id" : 6, "name" : "Jet Le", "date_join" : 1501057443631 }
{ "_id" : 7, "name" : "Tao Ten Hoa1", "date_join" : 1501057539265 }
{ "_id" : 8, "name" : "Harry Tran", "date_join" : 1501057559332 }
> db.users.find({date_join:{$gt:1501057559332}},{name:1, date_join:1}).pretty().sort({_id:1}).limit(4)
{ "_id" : 9, "name" : "Linh Tran", "date_join" : 1501057579787 }
{ "_id" : 10, "name" : "Cu Teo", "date_join" : 1501057620762 }
{ "_id" : 11, "name" : "Truong Giang", "date_join" : 1501057648154 }
{ "_id" : 23, "name" : "Tran Vuong", "date_join" : 1501830558229 }
> db.users.find({date_join:{$gt:1501830558229}},{name:1, date_join:1}).pretty().sort({_id:1}).limit(4)
{ "_id" : 24, "name" : "Old Man", "date_join" : 1502334052305 }
{ "_id" : 25, "name" : "Gai Ngoan", "date_join" : 1502334251405 }
{ "_id" : 26, "name" : "Trai Bao", "date_join" : 1502334460594 }
{ "_id" : 27, "name" : "Tran Vuong", "date_join" : 1503481813598 }
> 

Use the date_join to get the next one.

Get a count of total documents with MongoDB when using limit

No, there is no other way to get result and count total in one query. Two queries - one for count - one with limit. Or you have to use a different database. Apache Solr for instance works like you want. Every query there is limited andreturns totalCount.