Slow pagination over tons of records in mongodb

From MongoDB documentation:

Paging Costs

Unfortunately skip can be (very) costly and requires the server to walk from the beginning of the collection, or index, to get to the offset/skip position before it can start returning the page of data (limit). As the page number increases skip will become slower and more cpu intensive, and possibly IO bound, with larger collections.

Range based paging provides better use of indexes but does not allow you to easily jump to a specific page.

You have to ask yourself a question: how often do you need 40000th page? Also see this article;


I found it performant to combine the two concepts together (both a skip+limit and a find+limit). The problem with skip+limit is poor performance when you have a lot of docs (especially larger docs). The problem with find+limit is you can't jump to an arbitrary page. I want to be able to paginate without doing it sequentially.

The steps I take are:

  1. Create an index based on how you want to sort your docs, or just use the default _id index (which is what I used)
  2. Know the starting value, page size and the page you want to jump to
  3. Project + skip + limit the value you should start from
  4. Find + limit the page's results

It looks roughly like this if I want to get page 5432 of 16 records (in javascript):

let page = 5432;
let page_size = 16;
let skip_size = page * page_size;

let retval = await db.collection(...).find().sort({ "_id": 1 }).project({ "_id": 1 }).skip(skip_size).limit(1).toArray();
let start_id = retval[0].id;

retval = await db.collection(...).find({ "_id": { "$gte": new mongo.ObjectID(start_id) } }).sort({ "_id": 1 }).project(...).limit(page_size).toArray();

This works because a skip on a projected index is very fast even if you are skipping millions of records (which is what I'm doing). if you run explain("executionStats"), it still has a large number for totalDocsExamined but because of the projection on an index, it's extremely fast (essentially, the data blobs are never examined). Then with the value for the start of the page in hand, you can fetch the next page very quickly.


i connected two answer.

the problem is when you using skip and limit, without sort, it just pagination by order of table in the same sequence as you write data to table so engine needs make first temporary index. is better using ready _id index :) You need use sort by _id. Than is very quickly with large tables like.

db.myCollection.find().skip(4000000).limit(1).sort({ "_id": 1 });

In PHP it will be

$manager = new \MongoDB\Driver\Manager("mongodb://localhost:27017", []);
$options = [
            'sort' => array('_id' => 1),
            'limit' => $limit, 
            'skip' => $skip,

        ];
$where = [];
$query = new \MongoDB\Driver\Query($where, $options );
$get = $manager->executeQuery("namedb.namecollection", $query);

One approach to this problem, if you have large quantities of documents and you are displaying them in sorted order (I'm not sure how useful skip is if you're not) would be to use the key you're sorting on to select the next page of results.

So if you start with 

db.myCollection.find().limit(100).sort({created_date:true});

and then extract the created date of the last document returned by the cursor into a variable max_created_date_from_last_result, you can get the next page with the far more efficient (presuming you have an index on created_date) query

db.myCollection.find({created_date : { $gt : max_created_date_from_last_result } }).limit(100).sort({created_date:true});

Tags:

Mongodb

Recent Posts