Let's say I have a mongodb database with a collection of milions of documents. I have a python script that tries to fetch those documents, because of the input of the user, sometimes i have to filter the documents using:
field1
field2
field3
document fields, other times i only need field1, field2 and others only field1.
Should I create 3 indexes, one containing compound all the three fields, one with the subset of field1 and field2 and a third with only field1, or is it better to have only the first one?
I tried doing some experiments but i'm having quite inconsistent results.
Index created on {field1, field2, field3} (IN that order) can be used for three types of queries:
db.doc.find( field1= 'a val' ,field2='a val', field3='a val')
db.doc.find( field1= 'a val' ,field2='a val')
db.doc.find( field1= 'a val')
But it can NOT be used for the following:
db.doc.find( field2= 'a val')
So if you going query for exactly they way you mentioned => Create only one Index in that order.
If you going to query in different orders => you will need multiple indexes