Help me create a correct index for my data

Working with Data
,
1

A document in my student collection looks like this:

{
	"sample": false,
	"paid": true,
	"status": 1,
	"department": "1435334",
	"ts" : ISODate("2022-04-20T04:51:00.731Z"),
}

There are some records, which do not have department field. So another sample:

{
	"sample": true,
	"paid": true,
	"status": 4,
	"ts" : ISODate("2022-04-20T04:51:00.731Z"),
}

so I have a query which is like:

db.student.find({
	"paid": true,
	"sample": {"$ne": true},
	"status": {"$ne": 4},
}).sort({'ts': -1}).limit(10)

If it is being paginated, then:

db.student.find({
	"paid": true,
	"sample": {"$ne": true},
	"status": {"$ne": 4},
	"ts": {"$lt": createdAt},
}).sort({'ts': -1}).limit(10)

sometimes I need to find students who belong to departments:

db.student.find({
	"paid": true,
	"sample": {"$ne": true},
	"status": {"$ne": 4},
	"ts": {"$lt": createdAt},
	"department": {"$in": ["1", "2", "3"]}
}).sort({'ts': -1}).limit(10)

Now I want to know an optimal index which can cover these.

In my query, three things are always constant:

{ "paid": true, "sample": {"$ne": true}, "status": {"$ne": 4} }

so I want to add these to partial expression, so that my index has only these values.

Following ESR rule, I came with following index:

{
	"key" : {
		"status": 1,
		"department": 1,
		"ts": 1,
	},
	"name" : "idx_student_data",
	"background" : true,
	"partialFilterExpression" : {
		"paid" : true,
	}
}
  1. Is this index correct?
  2. Is this right way to use partial expressions?
  3. I am slightly confused about the ESR rule. Here department field in query is optional. do I need to create two indexes one with department and another without? It’s also in query, not equality.
2

If you want a partial index for

You could have that as partialFilterExpression: value rather than

Any reason why you have

rather than sample:false.

3

hey @steevej thank you for responding.

rather than sample:false .

this is because, the sample isn’t always present in all documents. So there three states:

  • field does not exist
  • sample is true
  • sample is false

So, in my query, I want to consider only the documents which don’t have sample field at all or sample field is set to false. How do I represent this in the partial filter expression?

4

I am also not on how to represent this on the partial filter exp:

"status": {"$ne": 4},

Since they don’t support $ne operator. My status value ranges from 0 to 4 and I want to avoid all the documents which have status as 4

5

I do not think you can but you might migrate your data so that sample is always there. This way you could add sample:false to your partialFilterExpression.

You are in luck with

as you could then use status:{$lt:4} to your filter.

6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.