Mongodb federated index and single field index perform better.

  mongodb, question

Now we have a performance problem. The solution is to add indexes to the fields. What is at issue now is the efficiency of field combination index or single index query?

Now the query fields are parentId and key, and the two fields are queried at the same time.

Now see index scheme is
1 Add indexes to ParentId and Key respectively

2 build a composite index {parentId:1,key:1} in this way:

Are the performance of these two queries similar?


In terms of index efficiency, it must be the joint index efficiency. In many cases, joint index should be considered if multiple fields are used for query.
However, things are not completely absolute, and the cost of indexing should also be taken into account.
Take your condition as an example, assumingkeyA record can be uniquely determined,parentIdIs there no need to add it?
Take a step back, even ifkeyCan’t determine only one, if it can determine the result set within a certain small range, such as 5 records, 10 records, thenparentIdThis condition is nothing more than scanning the 10 records again to find the appropriate record. I may choose not to put it in the joint index at all compared with the write, storage and memory overhead caused by adding it to the index.
The more records a condition can filter out, the better we say its “selectivity”. In general, when we build indexes, we put the conditions with better selectivity ahead and those with poor selectivity behind. Don’t put it in until it’s too bad.
This is actually a balance between time and space. Conditions put into the index save time (including CPU time and query time) and space (including storage space and memory space); Conditions not included in the index take time and space. Most of the time we expect to get the former, and when we choose the latter depends on your own assessment of the actual situation.