Slow Query when no data is returned

Hi All,

I am new to mongoDB I am facing a problem, I have created indexes for my collection. But when there is no record found it takes too much time to return a result. My collection is of around 1.8 million documents. Can someone please explain how does this work internally.

Hi @Tania_Garg, welcome to the MongoDB community!

Can you provide a little more information, such as an example of the document, indexes and queries that you say are slow?

Hi @Leandro_Domingues ,

Thanks for your reply. Please find below the document, query, and indexes. Please note that i have multiple arrays in one document and the query will include most of them. Whenever there is data for a particular query the response is very fast but in case of no record, it tends to slow down. Please suggest.

Document:

{
  "_id": {
    "$oid": "62c865378392121884010c67ce65418c"
  },
  "q_id": 1725833882,
  "pq_id":0,
  "data": [
    {
      "l_id": 1,
      "updated_date": "2022-09-26T18:14:02.729+05"
    },
    {
      "l_id": 2,
      "updated_date": "2022-09-26T18:14:02.729+05"
    }
  ],
  "sh_id": 6782,
  "level": 54,
  "status": 1,
  "tmp_id": {
    "$numberLong": "10"
  },
  "is_p": false,
  "t_data": [
    {
      "t_id": {
        "$numberLong": "303"
      },
      "t_name": "xyz",
      "t_v_id": {
        "$numberLong": "6544"
      },
      "t_v": "tre"
    },
    {
      "t_id": {
        "$numberLong": "487"
      },
      "t_name": "poi",
      "t_v_id": {
        "$numberLong": "65487"
      },
      "t_v": "ytre"
    }
  ],
  "bl_data": [
    {
      "s_m_id": 21107024369,
      "status": 1,
      "s_details": [
        {
          "s_id": 428097263500,
          "s_type": 809
        },
        {
          "s_id":4280876652,
          "s_type": 1954
        },
        {
          "s_id": 654378,
          "s_type": 857
        }
      ],
  "dl_id":12,
      "numbers": 4,
      "tts": 6000,
      "level": 4
    }
  ],
  "v_list": [
    {
      "v": "1",
      "used": false,
      "level":1
      "status": 1
    },
    {
      "v": "2",
      "used": true,
      "level":2
      "status": 1
    
  ],
  "qt_id":198275
}

Index:

1: qt_id,pq_id,bl_data.s_details.s_id,bl_data.numbers,dl_id,sh_id,bl_data.level
2: sh_id,v_list.level,level.status,pq_id

Query:

{
  "$and": [
    {
      "sh_id": {
        "$in": [
          17250
        ]
      },
      "pq_id": 0
    },
    {
      "v_list": {
        "$elemMatch": {
          "status": 1,
          "level": {
            "$in": [
              7,
              4,
              2,
              90,
              43
            ]
          },
          "used": true
        }
      }
    },
    {
      "data.l_id": {
        "$all": [
          1,
          2
        ]
      }
    },
    {
      "bl_data.s_details": {
        "$elemMatch": {
          "s_id": {
            "$in": [
              1870
            ]
          }
        }
      }
    },
    {
      "$and": [
        {
          "$or": [
            {
              "t_data.t_v_id": {
                "$in": [
                  4
                ]
              }
            },
            {
              "t_data.t_v_id": {
                "$in": [
                  1
                ]
              }
            }
          ]
        },
        {
          "$or": [
            {
              "t_data.t_v_id": {
                "$in": [
                  2
                ]
              }
            },
            {
              "t_data.t_v_id": {
                "$in": [
                  3
                ]
              }
            }
          ]
        }
      ]
    }
  ]
}

Hi @Sumanta_Mukhopadhyay ,

Thank you for explaining the working. I am facing a problem with no records situation, when the data is present the query response time is very fast like in milliseconds. In case of no record same query take seconds to return. Can you explain further that does this relates to caching or not? and in case of no records founds does it search indexes first and then also the whole document?

This is a bit misleading because it implies that MongoDB creates indexes automatically. It does not, and I don’t believe it ever will. Creating an index to support a query pattern should be a deliberate design decision, since every index has a price when writing to the collection, and there’s a limit of 64 indexes per collection.

@Tania_Garg in terms of your query performance, you might want to post:

  • the output of db.collection.explain('executionStats').find(...) for both cases where there are results (where it’s fast) and where there are no results (where it’s slow) so we can compare how the query planning and execution differ in both cases.
  • the output of db.collection.stats() to have a general overview of the collection itself.

One possible reason off the top of my head (a wild guess here): have you executed the query by iterating on the cursor? By default, the find() or aggregate() methods in most official drivers returns a cursor and do not actually execute the query unless the cursor is iterated on. It might be possible that you’re comparing two different things, as it’s strange that a query with a result and with no result that does similar work would be radically different in response times. Calling next() or toArray() – in Node – generally executes the cursor.

Best regards
Kevin

3 Likes