Having issues on performance quering arrays in collection

Working with Data
, ,
1

Hi Community Members

Could use some advice/help on performance on MongoDB.
I have a query that takes approx. 30-40 seconds. It uses index on the collection for one of the fields (OriginForm.MemberType), but as it is also selecting on a field in an array of objects, this seems to take quite some time, as there is no index on this.

I can add the result from the profiler, if desired.

And the .net core code that calls is this:

return await _individualRepository.GetMongoCollection().FindSync(x => x.CurrentStatus > 0 && x.InfoList != null && x.Email != null && x.OriginForm != null && x.OriginForm.MemberType == MemberType && x.InfoList.Any(y => y.SId == “Location” && y.Value == user.LocationId)).ToListAsync();

The collection “Individuals” consists of regular fields, as well as an Object OriginForm (where the MemberType is located) and an array InfoList, which contains 50 objects, each with 5 or 6 fields.

What I want to be able to create an index for, ideally, is the OriginForm.Membertype and the InfoList.9.Value.

I am uncertain if creating a Multikey index will solve this - and if it will not generate a huge index, with the many objects and fields in the array? And/or if it is possible to create an index that both covers the Array field (InfoList.9.Value) as well as the Object field (OriginForm.Membertype), or if that needs to be two seperate indexes.

Any suggestions on how to solve this the best possible way?

2

Here is the Profiler for the query:

{ op: 'query',
  ns: 'vennebasen.Individuals',
  command: 
   { find: 'Individuals',
     filter: 
      { CurrentStatus: { '$gt': 0 },
        InfoList: 
         { '$ne': null,
           '$elemMatch': { SId: 'Location', Value: '592e4f579b0fd2002046c49f' } },
        Email: { '$ne': null },
        OriginForm: { '$ne': null },
        'OriginForm.MemberType': 1 },
     '$db': 'vennebasen',
     lsid: { id: UUID("16355413-ba76-4c01-bb7a-6d264555ac69") },
     '$clusterTime': 
      { clusterTime: Timestamp(1, 1637075493),
        signature: 
         { hash: BinData(0, "RWdbYPli3A5fQfiwewbYJXPMGrY="),
           keyId: NumberLong("6977508361893838850") } } },
  keysExamined: 2714,
  docsExamined: 2714,
  cursorExhausted: true,
  numYield: 932,
  nreturned: 14,
  queryHash: 'F97ED1AB',
  planCacheKey: 'B22A1E69',
  locks: 
   { ParallelBatchWriterMode: { acquireCount: { r: 1 } },
     ReplicationStateTransition: { acquireCount: { w: 934 } },
     Global: { acquireCount: { r: 934 } },
     Database: { acquireCount: { r: 933 } },
     Collection: { acquireCount: { r: 933 } },
     Mutex: { acquireCount: { r: 1 } } },
  flowControl: {},
  storage: { data: { bytesRead: 26024273, timeReadingMicros: 12245112 } },
  responseLength: 491802,
  protocol: 'op_msg',
  millis: 31421,
  planSummary: 'IXSCAN { OriginForm.MemberType: 1 }',
  execStats: 
   { stage: 'CACHED_PLAN',
     nReturned: 14,
     executionTimeMillisEstimate: 31421,
     works: 15,
     advanced: 14,
     needTime: 0,
     needYield: 0,
     saveState: 932,
     restoreState: 932,
     isEOF: 1,
     inputStage: 
      { stage: 'FETCH',
        filter: 
         { '$and': 
            [ { InfoList: 
                 { '$elemMatch': 
                    { '$and': 
                       [ { SId: { '$eq': 'Location' } },
                         { Value: { '$eq': '592e4f579b0fd2002046c49f' } } ] } } },
              { CurrentStatus: { '$gt': 0 } },
              { Email: { '$not': { '$eq': null } } },
              { InfoList: { '$not': { '$eq': null } } },
              { OriginForm: { '$not': { '$eq': null } } } ] },
        nReturned: 14,
        executionTimeMillisEstimate: 30080,
        works: 2715,
        advanced: 14,
        needTime: 2700,
        needYield: 0,
        saveState: 932,
        restoreState: 932,
        isEOF: 1,
        docsExamined: 2714,
        alreadyHasObj: 0,
        inputStage: 
         { stage: 'IXSCAN',
           nReturned: 2714,
           executionTimeMillisEstimate: 43,
           works: 2715,
           advanced: 2714,
           needTime: 0,
           needYield: 0,
           saveState: 932,
           restoreState: 932,
           isEOF: 1,
           keyPattern: { 'OriginForm.MemberType': 1 },
           indexName: 'Membertype',
           isMultiKey: false,
           multiKeyPaths: { 'OriginForm.MemberType': [] },
           isUnique: false,
           isSparse: false,
           isPartial: false,
           indexVersion: 2,
           direction: 'forward',
           indexBounds: { 'OriginForm.MemberType': [ '[1, 1]' ] },
           keysExamined: 2714,
           seeks: 1,
           dupsTested: 0,
           dupsDropped: 0 } } },
  ts: 2021-11-16T15:12:13.375Z,
  client: '172.30.171.211',
  allUsers: