Single key and composite operation

Good morning everyone in my project I have a ‘T_summary’ collection with 2 indices. The first is on the freshman field and the second is a freshman compound index and data_invio

***running the find command
db.getCollection(‘T_summary’).find({
matricola: “71HBPA25”,
“data_invio”: {
$gte: new ISODate(“2000-01-01T00:00:01Z”)
}
}).explain(“executionStats”)

the index used is correct (matricola_1_data_invio_-1)
json
},
“winningPlan” : {
“stage” : “FETCH”,
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“matricola” : 1.0,
“data_invio” : -1.0
},
“indexName” : “matricola_1_data_invio_-1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“matricola” : ,
“data_invio” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“matricola” : [
“[“71HBPA25”, “71HBPA25”]”
],
“data_invio” : [
“[new Date(9223372036854775807), new Date(946684801000)]”
]
}
}
},

***running the find command
db.getCollection(‘T_summary’).find({
matricola: “71HBPA25”
}).explain(“executionStats”)

the index used is (matricola_1_data_invio_-1)
json
},
“winningPlan” : {
“stage” : “FETCH”,
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“matricola” : 1.0,
“data_invio” : -1.0
},
“indexName” : “matricola_1_data_invio_-1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“matricola” : ,
“data_invio” :
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“matricola” : [
“[“71HBPA25”, “71HBPA25”]”
],
“data_invio” : [
“[MaxKey, MinKey]”
]
}
}
},

but the correct one I expected to use is (only matricola_1)

Why does the composite index use the composite index in the second query if it has the single index?

Hi @Andrea_Loffredo,

Compound index can support queries that match on the prefix of the index fields. The (matricola_1_data_invio_-1) index will support queries on matricola filed as well as both matricola and data_invio field.

You can refer to the following Documentation links:

We also have a great community forum where you can have an in-depth conversation about specific use cases and interesting problems that you get to solve using MongoDB. I would highly recommend posting your question there for a more involved discussion.

Thanks,
Sonali

Hi Andrea,

Since your query has 2 conditions and both these columns are satisfied by composite index (matricola_1_data_invio_-1) instead of using single column index which you have on matricola alone. Even though the 2nd column in your composite index is reversed (-1).

A post was split to a new topic: Create Composite Key with interchangeable values