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 ;
querying isCoin:true/false updatedAt:-1
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
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.
@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.
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}