How complete should MongoDB indexes be?

For example, I have documents with only three fields: user, date, status. Since I select by user and sort by date, I have those tw开发者_运维知识库o fields as an index. That is the proper thing to do. However, since each date only has one status, I am essentially indexing everything. Is it okay to not index all fields in a query? Where do you draw the line?

What makes this question more difficult is the complete opposite approach to indexes between read-heavy and write-heavy collections. If yours is somewhere in between, how do you determine the proper approach when it comes to indexes?

Is it okay to not index all fields in a query?

Yes, but you'll want to avoid this for frequently used queries. Anything not indexed will imply a "table scan". This means accessing each possible document individually, which will be slow.

Where do you draw the line?

Also note, that if you sort by an un-indexed field, MongoDB will "yell at you" if you're trying to sort too much data. So you have to have some awareness of how much data is "outside of" the index.

If yours is somewhere in between, how do you determine the proper approach when it comes to indexes?

Monitoring, instrumenting, experimenting and experience.

There is no hard and fast rule here, it's all going to be about trade-offs. CPU vs. RAM vs. Disk IO vs. Responsiveness, etc.

The perfect situation is to store everything in a single index. By everything I mean all fields you query on, you sort by and you retrieve. This will ensure that you'll get maximum performance (if index fits in ram) This situation is not always possible, so you'll have to make choices.

Here are 3 tips to reduce at maximum the index size:

Does each of your query have a lot of results or only a few ? => A few : you do not have to index all the fields you retrieve (only the query and sort fields because few results mean few disk access).

Does your query results are often the same (i.e your working set is small) ? => don't index the field you retrieve because results are cached by mongodb.

Do you have a query field more selective than another ? => index the more selective field only.