Pagination with many elements is incredibly slow in Mongoose and Keystone

Question

Versions: Keystone v4

I have a Mongo database with >20k items. What I want is a paginator that would allow the user to quickly scroll through the Mongo database 25 elements at a time. Currently, this feature is implemented, but the server takes >40 seconds to return the results because it queries the entire (20k item) database. However, only 25 elements are displayed on a single page, so I feel like if it just fetches 25 results instead of 20k, it should be quicker. How could I implement this? I know about the .limit() function, but I can't seem to figure out pagination in keystone while using that.

Current Code:

var q = Items.model.find();
q.exec(function(err, newss) {
    console.log('There are %d', newss.length); // Prints out 20k number
    ...//skip

    locals.cnts = newss;

    // console.log(newss[0])

    locals.pagerr = pager({
        page: parseInt(req.query.page, 10) || 1,
        perPage: 25,
        total: newss.length
    });

    locals.itemsss = locals.cnts.slice(
        locals.pagerr.first - 1,
        locals.pagerr.last
    );
                
    next();

})

In it's current implmentation, it takes >40 seconds to return the paginated results. How can I fix this?

Answer 1

The model.find() function you're using here is equivalent to the Mongoose find() function. As you're calling it without any filters, this code is retrieving all 25k items from the database each time it runs. This data is being transferred to the web server/node process where the body of your function(err, newss) {...} function is run. Only then are the 25 items you're after being extracted from the set.

Instead, if you want to use offset-based pagination like this, you should be using the query.limit() and query.skip() functions. If you need to count the total items first, do so in a separate query using query.count().

I haven't tested this code (and it's been a while since I used Mongoose), but I think you want something like this:

// Warning! Untested example code
Items.model.find().count(function (err, count) {
  console.log('There are %d', count);

  locals.pager = pager({
    page: parseInt(req.query.page, 10) || 1,
    perPage: 25,
    total: count
  });

  Items.model.find()
    .skip(locals.pager.first)
    .limit(25)
    .exec(function(err, results) {
      locals.results = results;
      next();
    });
});

On a more general note – if you like Keystone and want to use Mongo, keep an eye on the Keystone 6 updates. Keystone 6 uses Prisma 2 as it's ORM layer and they recently released support for Mongo. As soon as that functionality production ready, we'll be supporting it in Keystone too.