How To Find Most Efficient Index Setup (Python Certification Exam)

Working with Data
, , ,
1

Does anyone here know the general concept of how to find the second most efficient index setup when given an aggregation?

For example lets say I have something like this:
db.collection.find(name: “Daniel”).sort(age: 1, height: 1)

I understand the ESR rule and that ultimately the most effective index for this would be:
db.collection.createIndex(name:1, age:1, height:1)

What confuses me is how I would go about finding the second most effective index. I thought that in order to find the second most effective I would simply invert all values. In this case everything would become -1.

However, according to the python practice certification problem I completed (which is extremely similar to my example with the only difference being the field names), instead it was stated that the proper second most effective answer would be:
db.collection.createIndex(name:1, age:-1, height:-1)

Can someone explain why this is? Does this have to do with ESR? I am wondering if maybe I simply need to invert the groupings in reverse order of ESR so that if I had any range values I would invert them. If I did not have range values then I invert all sort values as I do here.

I have been looking for information on this all over and have not been able to find anything useful the entire time. Any information on this would be helpful, thanks.

3

Hi @Daniel_R and welcome to MongoDB community forums!!

I would like to explain the indexes and the query using a more simple yet details format.

The query would utilise the below index definitions completely.
Case 1: ERS Rule:
Consider the query as: db.testI.find({x:1}).sort({y:1, z:1})
According to the ESR rule, the index definition would look like: db.testI.createIndex({x:1, y:1, z:1})

The part of the explain output shows that the index definition has been utilised:

....
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { x: 1, y: 1, z: 1 },
        indexName: 'x_1_y_1_z_1',
        isMultiKey: false,
        multiKeyPaths: { x: [], y: [], z: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: {
          x: [ '[1, 1]' ],
          y: [ '[MinKey, MaxKey]' ],
          z: [ '[MinKey, MaxKey]' ]
        }
      }
    },
....

Case 2: The following query db.testI.find({x:1}).sort({y:-1, z:-1}) would also utilise the same index definition used in Case 1 with equally effective result.

The explain output would look like:

...
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { x: 1, y: 1, z: 1 },
        indexName: 'x_1_y_1_z_1',
        isMultiKey: false,
        multiKeyPaths: { x: [], y: [], z: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'backward',
        indexBounds: {
          x: [ '[1, 1]' ],
          y: [ '[MaxKey, MinKey]' ],
          z: [ '[MaxKey, MinKey]' ]
        }
      }
    },
...

In this case, the direction is marked as backward while in the former case it is marked a s forward.

For the above query, if we follow the ESR rule, the most effective index would be db.testI.createIndex({x:1, y:-1, z:-1})
The explain output would look like:

...
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { x: 1, y: -1, z: -1 },
        indexName: 'x_1_y_-1_z_-1',
        isMultiKey: false,
        multiKeyPaths: { x: [], y: [], z: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'backward',
        indexBounds: {
          x: [ '[1, 1]' ],
          y: [ '[MinKey, MaxKey]' ],
          z: [ '[MinKey, MaxKey]' ]
        }
      }
....

In conclusion, the y:1, z:1 forms the S in ESR as a group. Thus, as long as it’s consistent (either y:1, z:1 or y:-1, z:-1 ) they should work the same way. This is not the case for y:1:, z:-1 or y:-1, z:1 though, since the ordering of y & z are opposites of each other.
For example, if the index is defined as:
db.testI.createIndex({x:1, y:-1, z:-1}) and the query is db.testI.find({x:1}).sort({y:1, z:1}) it would not use the index. The explain output would look like:

winningPlan: {
      stage: 'SORT',
      sortPattern: { y: 1, z: 1 },
      memLimit: 33554432,
      type: 'simple',
      inputStage: {
        stage: 'COLLSCAN',
        filter: { x: { '$eq': 1 } },
        direction: 'forward'
      }
    },

I hope I was able to answer your questions. Please feel free to reach out in case of any further queries.

Regards
Aasawari