1

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();
cmgchess
  • 7,996
  • 37
  • 44
  • 62
  • Generally, you should consider a Compound Index. Note that the order of the fields in the index matter. You can generate a Query plan, using the `explain()` on the `find` - the plan shows the usage of the index. – prasad_ Feb 24 '22 at 13:10

1 Answers1

1

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")
R2D2
  • 9,410
  • 2
  • 12
  • 28