Hi, I have a database with millions of records, let’s say 1M for simplicity. Now I want to write a single find query on the basis of 4 fields: field1, field2, field3 and field4 ( db.collection.find({field1: val1, field2: val2, field3: val3, field4: val4}) ). Currently fields 1 to 4 have single indexes on them. I have the following questions:
How much difference will writing a compound index on all the 4 fields create in the response time, as compared to having just the 4 single indexes?
With just the single indexes on the fields 1 to 4, does the order of writing the fields in the query affect the response time? If yes, then what should be the optimal strategy?
It depends on the cardinality of each field values. It depends if the fields value are muted once the documents are created.
Some of the facts are
There is one file per index, 1 compound index means 1 file, 4 separate indexes means 4 files.
The order of the fields in the query are not important but the order of the fields in a compound index are important. See the ESR rule.
If your query involves a field that is not in the best index for the query, then the document will have to be fetched.
But if your most frequent query always involves the 4 fields with equality, the I am pretty confident that the compound index will perform better most of the time.