How to index for three properties properly?

Hi , I have posts collection and fetch the datas depending on three properties.
I have two types of posts which are seperated with the boolean isCoin:true and isCoin:false.
I also always use updatedAt:-1 when querying.
So, I have two scenarios ;

  1. querying isCoin:true/false updatedAt:-1
  2. coinid:givenCoinId , updatedAt:-1

in the first scenario i dont use coinid when fetching , I just fetch them depending on isCoin property and updatedAt descending

in the second scenario I use coinid and updatedAt descending,

so what kind of Indexing should I use to optimise those request ?
in total I have three varieables to be taken into account → isCoin , updatedAt:-1 and coinid(objectid)
thanks.

summary:
I have three properties isCoin updatedAt:-1 and coinid(objectid)
I have two query scenarios
1-) isCoin:true or false and updatedAt:-1
2-) coinid(objectid) and updatedAt:-1

Hello @iktisat_ogrencisi, welcome to the MongoDB Community forum!

I think, you need to use two indexes - one for each query. One of the factors to consider is the order of the fields you specify in creating the index. For example,

db.collection.createIndex( { isCoin: 1, updatedAt: -1 } )
is different from:
db.collection.createIndex( { updatedAt: -1, isCoin: 1 } )

This is something you need to figure based upon how your data is. The Query Selectivity is taken into consideration to determine the order of the fields in the index. Note that the order of fields specified in the query filter do not matter in your case. In my opinion, isCoin may not be a very selective, to be used as the first field in the index.

You can use the explain method on the query, and it generates a query plan for that query. The query plan tells if the query is using an index or not, or if there are multiple indexes which one of them is being used, or no index is being used at all. Also, there are options/modes to see the information like the amount of time the query takes using the index, etc.

1 Like

thank you for answering, Lastly, what if %60 percent of my posts have isCoin:true , should I still use isCoin as second field in index?

@iktisat_ogrencisi, I guess you can, as it comes as the second field. The concern would be, if the 60% is a large number of documents it is probably waste of index storage and loading into the memory. You can do some trials with sample data sets and see how it works for you.

1 Like

@iktisat_ogrencisi ,

Those scenarios require 2 indexes as MongoDB will not use a second field as a leading filter if the first indexed field is absent.

{isCoin : 1, updatedAt : -1}
{coinId : 1, updatedAt -1}

Maintaining those indexes shouldn’t be much of overhead and it will speed up the queries so you need to go for it.

In summary indexes need to be created for each unique query shape in Equity Sort Range field order .

Thanks
Pavel

1 Like

Thank you very much ! Ah , I almost forgot to ask, should I always define a new index foreach different sceneario ? for example I also sometimes need to fetch posts depending on ownerid of the post? so there should also be index like {ownerid:!,updatedAt:-1} ?

Thank you a lot for your explanation. I really got it now.
One last I wonder about it → Should I define another index for another scenario where I query posts for ownerid property ? , or in other words , are indices set depending on their query scenario ? like {ownerid:1,updatedAt:-1}

Yes, a different compound index on the two fields will help improve query performance.

2 Likes

If you have a lot of properties to index (i would say more than 10 you may consider an attribute pattern schema:

1 Like

Thank you a lot Pavel, I learnt so many things today.

Thank you a lot Prasad, I learnt so many things today.

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