I have a big mongodb query that has some dynamic properties based on filter options, and including filtering between dates. My query is currently causing scanned Objects / returned results ratio to go above 1000. I am sure my query can be improved as well as adding suitable indexes but I am not sure of the correct index's / improvements to my query.
const userFilter = user ? { assignee: new Types.ObjectId(user) } : null;
const clientFilter = client ? { client: new Types.ObjectId(client) } : null;
Collection.aggregate([
{
$match: {
...userFilter,
...clientFilter,
status: 1,
customer: new Types.ObjectId(customer),
},
},
{
$match: {
$or: [
{
end: {
$gte: new Date(end),
},
start: {
$lte: new Date(start),
},
},
{
end: {
$gte: new Date(end),
},
start: {
$lte: new Date(end),
$gte: new Date(start),
},
},
{
start: {
$lte: new Date(start),
},
end: {
$lte: new Date(end),
$gte: new Date(start),
},
},
{
start: {
$gte: new Date(start),
},
end: {
$lte: new Date(end),
},
},
{
start: {
$lte: new Date(end),
},
// @ts-ignore
start: {
$gte: new Date(start),
},
},
{
end: {
$lte: new Date(end),
},
// @ts-ignore
end: {
$gte: new Date(start),
},
},
],
},
},
{
$sort: { createdAt: 1 },
},
}
Depending on filter options we could be filtering by assignee and/or client.
We also only want returned results where the start or end date of the document falls within the start and end date filters.
I have tried a few variations of the query itself, as well as adding some compound indexes but had no real success improving the query or index's.
Copyright Notice：Content Author:「user18196624」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/74666898/complex-query-index-help-for-mongodb-query