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

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.

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