I had a collection that was growing and with it's growth had a crazy increase in query response times.

The collection records looked something like this:

    orderStat: { type: String },
    items: { type: Array },
    docBigValue: { type: String },
    detailsBigValue: { type: Object }

I did everything I could think of like ensure I was indexing on queried fields, use .lean(), excluded the fields (docBigValue, detailsBigValue) with huge values from queries, limit the number of records returned per query, stopped allowing fuzzy search using like. Everything i did made an improvement but the rate of new records quickly made the value behind each improvement lost.

Today I looked at the collections avgObjSize and found it was over 73,000 while most collections had a value under 500. I took a backup and then deleted one of the large fields from all the records. My query response time went from over 6 seconds to under half a second.

Why does the size of a field in a collection affect query response times even when that field is excluded from the query? Is this a bug or normal?

Mongodb 4.x

In my case a simple db.someCollection.find({}, {docBigValue: 0, detailsBigValue: 0 }) could be used to reproduce the issue. I just can't find anything in the documentation that would suggest the size of a field would affect query performance even when it's excluded.

  • When the query is made to search all rows, it is clear that this more time with a bigger size. did you make all optimization efforts btaht are possible – nbk Apr 3 at 18:28
  • Please post a sample query you are working with for a discussion. There are many factors that affect a query's performance - network, size of collection, document size, indexes, RAM, etc. You can use the explain method to verify the query plan and see what is happening. – prasad_ Apr 5 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.