MongoDB, Mongoose - Slow query when fetching 10k+ documents

Question

I have a MongoDB database with 10-12k documents in a collection and I'm experiencing really slow queries when attempting to fetch all documents, like this:

Sales.find()
    .where('author').equals(author)
    .where('date').gt(startDate.unix()).lt(endDate.unix())
    .exec(function(err, results) {
        callback();
    });

This query fetches around 10.5k documents and it takes 1000-1300ms to execute. I tried removing the "where" conditions - it only makes it slower (more documents fetched?).

Does the problem come from Mongoose, MongoDB, JavaScript or Node? I used to run PHP/MySQL database and it was 10-20 times faster in similar conditions, like fetching 10k+ rows of data. What am I doing wrong?

EDIT

Sales schema:

var salesSchema = new Schema({
    author: String,
    kind: String,
    productID: String,
    description: String,
    date: String,
    amount: String,
    transactionID: {
        type: String,
        unique : true
    }
});

Query result from the RoboMongo desktop client:

db.getCollection('sales').find({}).explain()

executionTimeMillis: 46
nReturned: 10359

Can you edit your question to include the schema definition of the Sales model, any indexes on the underlying collection, output from the .explain() query plan for the mongo shell db.collection.find() method with the sample query? — chridam
I can see date is a String but your mongoose Sales.find() method is doing a date range query based on a unix timestamp field which is an integer, also it looks like you don't have any indexes defined on the date field so there might be a possibility that mongodb is doing a full collection scan. Can you also add the output from db.getCollection('sales').find({author: author, date {gt: startDate, lt: endDate }}).explain()? — chridam
Something is wrong I think. "db.getCollection('sales').find({author: 'nickys', date: {gt: '1325336400', lt: '1420030800' }}).explain()" doesn't return any results. I tried removing the quotes from the timestamps, but it didn't help. It works if I remove the entire date condition. Forgive me, I'm new to Mongo :) — Nikolay Dyankov
I will read more about those indexes and update the post when I run the tests again. Thanks! — Nikolay Dyankov

Nikolay Dyankov Nikolay Dyankov · Accepted Answer · 2015-10-28T12:17:52

The problem came from Mongoose. By default, find() will return documents as Mongoose Documents, which costs a lot. By adding lean() to the query, documents are returned as plain JavaScript objects and for this case of 10k+ returned documents, the query time got reduced 3-5 times.

Sales.find()
    .where('author').equals(author)
    .where('date').gt(startDate.unix()).lt(endDate.unix())
    .lean()
    .exec(function(err, results) {
        callback();
    });

Read more here: http://www.tothenew.com/blog/high-performance-find-query-using-lean-in-mongoose-2/

MongoDB, Mongoose - Slow query when fetching 10k+ documents

1 Answers