I am having a lot of difficulty creating indexes that a query actually uses.
From the cloud console I find a slow query and try to index but the index is not used.
What index would be suggested for the query below
{
"type": "command",
"ns": "ONSITEDb.JobONSITE",
"appName": "MongoDB Compass",
"command": {
"explain": {
"find": "JobONSITE",
"filter": {
"type": "command",
"ns": "ONSITEDb.JobONSITE",
"command": {
"aggregate": "JobONSITE",
"pipeline": [
{
"$match": {
"isComplete": false,
"isShadow": false
}
},
{
"$sort": {
"startTime": -1
}
},
{
"$skip": 0
},
{
"$limit": 25
}
],
"allowDiskUse": true,
"collation": {
"locale": "en",
"caseLevel": false,
"caseFirst": "off",
"strength": 1
},
"cursor": {},
"$db": "ONSITEDb",
"lsid": {
"id": {
"$uuid": "d9501227-e952-4804-90e5-5e9ecffae67d"
}
},
"$clusterTime": {
"clusterTime": {
"$timestamp": {
"t": 1639458374,
"i": 4
}
},
"signature": {
"hash": {
"$binary": "TDu/Mdszf8VHazHhf92PrXeSQWM=",
"$type": "00"
},
"keyId": {
"$numberLong": "7005581243098071042"
}
}
}
},
"planSummary": "IXSCAN { isShadow: 1, isComplete: 1, startTime: -1 }",
"keysExamined": 4524,
"docsExamined": 25,
"hasSortStage": true,
"fromMultiPlanner": true,
"cursorExhausted": true,
"numYields": 214,
"nreturned": 25,
"queryHash": "B2211B92",
"planCacheKey": "B572E649",
"reslen": 9276866,
"locks": {
"Global": {
"acquireCount": {
"r": 215
}
},
"Mutex": {
"acquireCount": {
"r": 1
}
}
},
"readConcern": {
"level": "local",
"provenance": "implicitDefault"
},
"writeConcern": {
"w": "majority",
"wtimeout": 0,
"provenance": "implicitDefault"
},
"storage": {
"data": {
"bytesRead": 1361197792,
"timeReadingMicros": 3505870
},
"timeWaitingMicros": {
"cache": 1133
}
},
"remote": "13.236.230.61:42954",
"protocol": "op_msg",
"durationMillis": 3880,
"v": "5.0.5"
},
"skip": 0,
"limit": 0,
"maxTimeMS": 60000
},
"verbosity": "allPlansExecution",
"lsid": {
"id": {
"$uuid": "1417e28f-1176-436e-9e1d-878c963765d7"
}
},
"$clusterTime": {
"clusterTime": {
"$timestamp": {
"t": 1639478103,
"i": 1
}
},
"signature": {
"hash": {
"$binary": "tepwiDQ0tMz8HoJ1a3ln5PPT6r8=",
"$type": "00"
},
"keyId": {
"$numberLong": "7005581243098071042"
}
}
},
"$db": "ONSITEDb"
},
"numYields": 70,
"reslen": 7880,
"locks": {
"Global": {
"acquireCount": {
"r": 71
}
},
"Mutex": {
"acquireCount": {
"r": 1
}
}
},
"readConcern": {
"level": "local",
"provenance": "implicitDefault"
},
"storage": {
"data": {
"bytesRead": 442375998,
"timeReadingMicros": 1103006
},
"timeWaitingMicros": {
"cache": 34
}
},
"remote": "203.220.178.172:62443",
"protocol": "op_msg",
"durationMillis": 1136,
"v": "5.0.5"
}