Which index is better to use in the following case

[Gased_N_A]

I’m fetching messages from my collection, here’s my query:

db.messages.find({chatId: "60f6926e9f115c0105bc3157", isDeleted: false, visibleTo: {$in: ["60037fd276d620ee5d7ae2d1"]}})

.projection({})
.sort({_id:-1})
.limit(5)

I’m wondering which is index is better in my case, this one:

await messages.createIndex({visibleTo: -1, chatId: -1, _id: -1}, {background: true, unique: true});

or this:

await messages.createIndex({visibleTo: -1, chatId: -1}, {background: true});

However, explain returns the same result. See:

“Documents examined” is 6 not 5 because I have a document with “isDeleted” equals to “true”

[Pavel_Duchovny]

Hi @Gased_N_A ,

Index field order should be in a Equality Sort Range order.

Performance Best Practices: Indexing | MongoDB Blog

Its best to cover as many filtered fields as possible as long as write throughput is not impacted by too many indexes.

For your query the best index seems to be actually a partial index on isDeleted: false with index fields :

{ chatId: 1, visbleTo : 1, _id: -1}

Partial Indexes — MongoDB Manual

However, if queries can have isDeleted : true in some cases I suggest to add isDeleted to the index:

{ chatId: 1, visbleTo : 1, isDeleted : 1, _id: -1}

Thanks
Pavel

[Gased_N_A]

Thank you so much for your reply, really appreciate your time.

I was wondering, why all the fields except _id has a ascending order (chatId: 1).

One more thing, I have never worked with partial indexes. Why would it be better in my case? I don’t have any other queries to this collection. On what’s the filter query I shall use to create the partial index?

Once again thank you so much for your help

[Pavel_Duchovny]

Hi @Gased_N_A ,

Partial indexes index only documents that answer a specific criteria. In this case only isDeleted : false.

This makes the index small as possible for this query while focusing on not deleted marked messages.

If this criteria is present in all queries of this type it saves another indexed field as the system knows only false values are in this index.

{ partialFilterExpression: { isDeleted : false} }

Regarding the indexing order , having asc order is the default for equality fields but for sort fields its best to use the query order in the sort. In your case the only field that makes a difference is the _id which is sorted desc order thats why its the only one really need -1.

Thanks
Pavel

[Gased_N_A]

You’re amazing man, thank you so much.