Mongoose limit/offset and count query

Question

Bit of an odd one on query performance... I need to run a query which does a total count of documents, and can also return a result set that can be limited and offset.

So, I have 57 documents in total, and the user wants 10 documents offset by 20.

I can think of 2 ways of doing this, first is query for all 57 documents (returned as an array), then using array.slice return the documents they want. The second option is to run 2 queries, the first one using mongo's native 'count' method, then run a second query using mongo's native $limit and $skip aggregators.

Which do you think would scale better? Doing it all in one query, or running two separate ones?

Edit:

// 1 query
var limit = 10;
var offset = 20;

Animals.find({}, function (err, animals) {
    if (err) {
        return next(err);
    }

    res.send({count: animals.length, animals: animals.slice(offset, limit + offset)});
});


// 2 queries
Animals.find({}, {limit:10, skip:20} function (err, animals) {            
    if (err) {
        return next(err);
    }

    Animals.count({}, function (err, count) {
        if (err) {
            return next(err);
        }

        res.send({count: count, animals: animals});
    });
});

Answer 1

I suggest you to use 2 queries:

1. db.collection.count() will return total number of items. This value is stored somewhere in Mongo and it is not calculated.

2. db.collection.find().skip(20).limit(10) here I assume you could use a sort by some field, so do not forget to add an index on this field. This query will be fast too.

I think that you shouldn't query all items and than perform skip and take, cause later when you have big data you will have problems with data transferring and processing.

Answer 2

Instead of using 2 separate queries, you can use aggregate() in a single query:

Aggregate "$facet" can be fetch more quickly, the Total Count and the Data with skip & limit

    db.collection.aggregate([

      //{$sort: {...}}

      //{$match:{...}}

      {$facet:{

        "stage1" : [ {"$group": {_id:null, count:{$sum:1}}} ],

        "stage2" : [ { "$skip": 0}, {"$limit": 2} ]
  
      }},
     
     {$unwind: "$stage1"},
  
      //output projection
     {$project:{
        count: "$stage1.count",
        data: "$stage2"
     }}

 ]);

output as follows:-

[{
     count: 50,
     data: [
        {...},
        {...}
      ]
 }]

Also, have a look at https://docs.mongodb.com/manual/reference/operator/aggregation/facet/

Answer 3

db.collection_name.aggregate([
    { '$match'    : { } },
    { '$sort'     : { '_id' : -1 } },
    { '$facet'    : {
        metadata: [ { $count: "total" } ],
        data: [ { $skip: 1 }, { $limit: 10 },{ '$project' : {"_id":0} } ] // add projection here wish you re-shape the docs
    } }
] )

Instead of using two queries to find the total count and skip the matched record.
$facet is the best and optimized way.

1. Match the record
   
2. Find total_count
   
3. skip the record
   
4. And also can reshape data according to our needs in the query.

Answer 4

There is a library that will do all of this for you, check out mongoose-paginate-v2

Answer 5

After having to tackle this issue myself, I would like to build upon user854301's answer.

Mongoose ^4.13.8 I was able to use a function called toConstructor() which allowed me to avoid building the query multiple times when filters are applied. I know this function is available in older versions too but you'll have to check the Mongoose docs to confirm this.

The following uses Bluebird promises:

let schema = Query.find({ name: 'bloggs', age: { $gt: 30 } });

// save the query as a 'template'
let query = schema.toConstructor();

return Promise.join(
    schema.count().exec(),
    query().limit(limit).skip(skip).exec(),

    function (total, data) {
        return { data: data, total: total }
    }
);

Now the count query will return the total records it matched and the data returned will be a subset of the total records.

Please note the () around query() which constructs the query.

Answer 6

You don't have to use two queries or one complicated query with aggregate and such.

You can use one query

example:

const getNames = async (queryParams) => {

  const cursor = db.collection.find(queryParams).skip(20).limit(10);
  return {
    count: await cursor.count(),
    data: await cursor.toArray()
  }
  
}

mongo returns a cursor that has predefined functions such as count, which will return the full count of the queried results regardless of skip and limit

So in count property, you will get the full length of the collection and in data, you will get just the chunk with offset of 20 and limit of 10 documents

Answer 7

Thanks Igor Igeto Mitkovski, a best solution is using native connection

document is here: https://docs.mongodb.com/manual/reference/method/cursor.count/#mongodb-method-cursor.count and mongoose dont support it ( https://github.com/Automattic/mongoose/issues/3283 )

we have to use native connection.

const query = StudentModel.collection.find(
    {
       age: 13
    }, 
    {
       projection:{ _id:0 }
    }
    ).sort({ time: -1 })
const count = await query.count()
const records = await query.skip(20)
          .limit(10).toArray()