CODEWITHSUNDEEP
mongodbindexingnosqlquery-optimizationmongodb-indexes
cmgchess
cmgchess

Reputation: 10247

In MongoDB how to decide for a collection which fields to be indexed for a costly query

I have a collection with 1000+ records and I need to run the query below. I have come across the issue that this query takes more than a minute even if the departmentIds array has length something like 15-20. I think if I use an index the query time will be reduced.
From what I observe the 99% of the time spent on the query is due to the $in part.
How do I decide which fields to index. Should I index only department.department_id since that's what taking most time or should I create a compound index using userId,something and department.department_id (bascially all the fields I'm using in the query here)

Here is what my query looks like

    let departmentIds = [..........................  can be large]

    let query = {
      userId: someid,
      something: something,
      'department.department_id': {
        $in: departmentIds
      }
    };

//db query
   let result =  db
    .collection(TABLE_NAME)
    .find(query)
    .project({
      anotherfield: 1,
      department: 1
    })
    .toArray();

Upvotes: 1

Views: 95976

Answers (1)

R2D2
R2D2

Reputation: 10707

You need to check all search cases and create indexes for those that are often used and most critical for your application. For the particular case above this seems to be the index options:

  1. userId:1
  2. userId:1,something:1
  3. userId:1,something:1,department.department_id:1

I bet on option 1 since userId sounds like a unique key with high selectivity very suitable for index , afcourse best is to do some testing and identify the fastest options , there is good explain option that can help alot with the testing:

  db.collection.find().explain("executionStats")

Upvotes: 1

Related Questions