Question regarding single index and compound indexes

Sushant_K · 2024-08-07T09:43:53.338Z

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?

steevej · 2024-08-07T21:24:54.474Z

It depends.

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.

steevej · 2024-08-13T13:27:19.449Z

@Sushant_K, a followup on this would be appreciated. If you made any progress, please share.