CODEWITHSUNDEEP
mongodbmongodb-querymongodb-.net-driver
ronanduffey
ronanduffey

Reputation: 46

MongoDB Index Strategy For Many Query Possibilities

I have a MongoDB collection that holds some data. I have simplified the below example but imagine each object has 10 keys, their data types being a mixture of numbers, dates and arrays of numbers and sub-documents.

{
    '_id': ObjectId,
    A: number,
    B: number,
    C: datetime,
    D: [
        number, number, number
    ]
}

I have an application that can send queries against any of the keys A, B, C and D in any combination, for example { A: 1, C: 'ABC' } and { B: 10: D: 2 }. Aside from a couple of fields, it is expected that each query should be performant enough to return in under 5 seconds.

I understand MongoDB compound indexes are only used when the query key order matches that of the index. So even if made an index on every key { A: 1, B: 1, C: 1, D: 1 }, then queries to { A: 2, D: 1 ] would not use the index. Is my best option therefore to make indexes for every combination of keys? This seems quite arduous given the number of keys on each document, but unsure how else I could solve this? I have considered making all queries query each key, so that the order is always the same, but unsure how I could write a query when a particular key is not queried. Example, application wants to query on some value of B but would also need

{
    A: SomeAllMatchingValue?,
    B: 1:,
    C: SomeAllMatchingValue?,
    D: SomeAllMatchingValue? 
}

I am wondering if keeping the least queried fields to the last in the query would make sense, as then index prefixes would work for the majority of commoner use cases, but reduce the number of indexes that need to be generated.

What would be the recommended best practice for this use case? Thanks!

EDIT:

Having researched further and I think the attribute pattern is the way to go. The document keys that are numeric could all be moved into attributes and one index could cover all bases.

https://www.mongodb.com/blog/post/building-with-patterns-the-attribute-pattern

Upvotes: 0

Views: 93

Answers (1)

ray
ray

Reputation: 15227

Your case seems a perfect use case of wildcard index, which is introduced in MongoDB v4.2+.

You can create a wildcard index of all top-level fields like this:

db.collection.createIndex( { "$**" : 1 } )

Running with arbitrary criteria:

{
  D: 3,
  B: 2,
}

or

{
  A: 1,
  C: ISODate('1970-01-01T00:00:00.000+00:00')
}

will results in IXSCAN in explain():

{
  "explainVersion": "1",
  "queryPlanner": {
    ...
    "parsedQuery": {
      "$and": [
        ...
      ]
    },
    ...
    "winningPlan": {
      "stage": "FETCH",
      "filter": {
        "C": {
          "$eq": {
            "$date": {
              "$numberLong": "0"
            }
          }
        }
      },
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "$_path": 1,
          "A": 1
        },
        "indexName": "$**_1",
        ...
        "indexBounds": {
          ...
        }
      }
    },
    "rejectedPlans": [
      ...
    ]
  },
  ...
}

Upvotes: 1

Related Questions