How to do Indexing on MongoDB dynamic generated query in aggregation pipeline?

  Kiến thức lập trình

How can i use index on dynamic query generated fields. Suppose i have 20 query params. We are able to search with either single param or multiple params. If it’s single param, i could just use index on that database field. But what about if searching with multiple query param? My aggregation query is being built from c# Linqkit and i did check it from query profiler to identify slow queries.

{
  "type": "command",
  "ns": "UserCollection",
  "command": {
    "aggregate": "UserCollection",
    "pipeline": [
      {
        "$match": {
          "$and": [
            {
              "PreferredCategory": {
                "$ne": null
              }
            },
            {
              "$expr": {
                "$anyElementTrue": {
                  "$map": {
                    "input": "$PreferredCategory",
                    "as": "y",
                    "in": {
                      "$eq": [
                        {
                          "$toString": "$$y._id"
                        },
                        "-1"
                      ]
                    }
                  }
                }
              }
            },
            {
              "AcademicQualifications": {
                "$ne": null
              }
            },
            {
              "AcademicQualifications": {
                "$elemMatch": {
                  "IsForeigner": false
                }
              }
            }
          ]
        }
      },
      {
        "$project": {
          "_id": "$_id",
          "UpdatedOn": "$UpdatedOn",
          "Id": "$Id",
          "Address": "$Address",
          "Name": "$Name",
        
        }
      },
      {
        "$sort": {
          "Id": 1
        }
      },
      {
        "$skip": 0
      },
      {
        "$limit": 25
      }
    ],
    "cursor": {},

    "$clusterTime": {
      "clusterTime": {
        "$timestamp": {
          "t": 1718085612,
          "i": 3
        }
      },
     
  "planSummary": "COLLSCAN",
  "keysExamined": 0,
  "docsExamined": 5213428,
  "hasSortStage": true,
  "cursorExhausted": true,
  "numYields": 23701,
  "nreturned": 0,
  "queryHash": "FB6AE820",
  "planCacheKey": "8A0D99CD",
  "queryFramework": "classic",
  "reslen": 246,
  "locks": {
    "FeatureCompatibilityVersion": {
      "acquireCount": {
        "r": 23703
      }
    },
    "Global": {
      "acquireCount": {
        "r": 23703
      }
    },
    "Mutex": {
      "acquireCount": {
        "r": 2
      }
    }
  },
  "readConcern": {
    "level": "local",
    "provenance": "implicitDefault"
  },
  "writeConcern": {
    "w": "majority",
    "wtimeout": 0,
    "provenance": "implicitDefault"
  },
  "storage": {
    "data": {
      "bytesRead": 24247394382,
      "timeReadingMicros": 181767420
    },
    "timeWaitingMicros": {
      "cache": 47581
    }
  },

  "protocol": "op_msg",
  "durationMillis": 322465,

  "isTruncated": false
}

This query is being generated and it’s taking a lot time. I did use indexing on Id but it is not being used. So, as it can be dynamic query, how can i do indexing? as there are 20 query parameter, do i need to use every possible single and compound index?

LEAVE A COMMENT