Mongoose limit/offset and count query

问题:

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

回答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.

回答2:

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.