Redsandro,
You should consider Index Cardinality and Selectivity .
1. Index power
The power indicator indicates the number of possible values for the field. The sex field has only two possible values. It has a very low power . Other fields, such as names, usernames, phone numbers, emails , etc., will have a more unique meaning for each document in the collection, which is considered high power .
High power
The greater the power of the field, the more useful the index will be, because indexes narrow the search space, making it much smaller.
If you have a pointer to sex and you are looking for men named John. You would only narrow the result space by about% 50 if you indexed sex first. Conversely, if you indexed name , you immediately narrowed down the result set to the smallest part of users named John, then you will refer to these documents to check the gender.
Rule of thumb
Try creating indexes on the high-cardinality keys or place the high-cardinality keys first in the composite index. Read more about this in the section on complex indexes in the book:
MongoDB Ultimate Guide
2. Selectivity
In addition, you want to use indexes selectively and write queries that limit the number of possible documents with an indexed field. To keep it simple, consider the following collection. If your index is {name:1} , if you run the query { name: "John", sex: "male"} . You will have to scan the document 1 . Because you allowed MongoDB to be selective.
{_id:ObjectId(),name:"John",sex:"male"} {_id:ObjectId(),name:"Rich",sex:"male"} {_id:ObjectId(),name:"Mose",sex:"male"} {_id:ObjectId(),name:"Sami",sex:"male"} {_id:ObjectId(),name:"Cari",sex:"female"} {_id:ObjectId(),name:"Mary",sex:"female"}
Consider the following compilation. If your index is {sex:1} , if you run the query {sex: "male", name: "John"} . You will have to scan documents 4 .
{_id:ObjectId(),name:"John",sex:"male"} {_id:ObjectId(),name:"Rich",sex:"male"} {_id:ObjectId(),name:"Mose",sex:"male"} {_id:ObjectId(),name:"Sami",sex:"male"} {_id:ObjectId(),name:"Cari",sex:"female"} {_id:ObjectId(),name:"Mary",sex:"female"}
Imagine possible differences in a larger dataset.
A little explanation of composite indexes
It is easy to make the wrong assumption about composite indices. According to the MongoDB Indexes Guide .
MongoDB supports composite indexes, where a single index structure contains links to multiple fields in collection documents. The following diagram illustrates an example of a composite index into two fields:
When creating a composite index 1, the index will contain several fields. Therefore, if we index the collection using {"sex" : 1, "name" : 1} , the index will look something like this:
["male","Rick"] -> 0x0c965148 ["male","John"] -> 0x0c965149 ["male","Sean"] -> 0x0cdf7859 ["male","Bro"] ->> 0x0cdf7859 ... ["female","Kate"] -> 0x0c965134 ["female","Katy"] -> 0x0c965126 ["female","Naji"] -> 0x0c965183 ["female","Joan"] -> 0x0c965191 ["female","Sara"] -> 0x0c965103
If you index the collection using {"name" : 1, "sex" : 1} , the index will look something like this:
["John","male"] -> 0x0c965148 ["John","female"] -> 0x0c965149 ["John","male"] -> 0x0cdf7859 ["Rick","male"] -> 0x0cdf7859 ... ["Kate","female"] -> 0x0c965134 ["Katy","female"] -> 0x0c965126 ["Naji","female"] -> 0x0c965183 ["Joan","female"] -> 0x0c965191 ["Sara","female"] -> 0x0c965103
Having {name:1} as your Prefix , you will be much better off using compound indexes. There is much more that can be read on this topic, I hope this can provide some clarity.