Indexing strategi

[Andreas Bernhardsson]

I have some questions regarding the order of fields in indexes.

I have a quite large collection of documents which each has an "Owner" and "Status".

At the moment I have a number of compound indexes on first "Owner" and then "Status" and then some other fields that different queries use.

This collection has about 15 000 000 entries, there are about 200 000 possible owners quite evenly distributed across the collection.

Then we have a "Status" fields which basically tells if the document should be a part of the search or not, this one would cut down the amount entries to 1/3.

I read in the MongoDB documentation about the database tries to hold the "right-most" values in RAM, would creating the index on first "Status" and then "Owner" (instead of the other way around) aid the database in doing this?

A query mostly looks something like these:

{

 Owner:{'$in':{1,2,4,6, and so on with up to 1000 entries... }},

 Status: 1,

 etc: ...

}

Best Regards

Andreas Bernhardsson

[Emily S]

Hi Andreas

MongoDB will only keep the right-most (or left-most, depending on whether the index is ascending or descending) values in RAM if the indexed field incrementally grows with every insert and most queries select recently added documents.

Given that you have 200 000 possible owners, evenly distributed across 15 000 000 documents, and only 3 possible statuses, your compound index on Owner, then Status is the right way to go.