Can a Compound Index serve multiple queries

http://stackoverflow.com/questions/18698024/can-a-compound-index-serve-multiple-queries

Q:

I have a collection by name factories .

On this collection there will be two types of queries that will be hit on the collection named factories , shown below

db.factories.find({ city: "New York", state: "NY"} );


db.factories.find({ city: "New York", state: "NY" , country:"US"} );

My question is if i create a compound index as shown below , will it serve both the queries ??

db.factories.ensureIndex({city:1,state:1,country:1},{"unique" : false})

A:

Yes.

To understand why the answer is yes we need to talk about how compound indexes are actually built up.

The fields within a compound index go from first to last with the values of all children nested within the parents. This means that if you had three documents like:

[{
    _id:{},
    a: 1,
    b: 2,
    c: 3
},{
    _id:{},
    a: 4,
    b: 5,
    c: 6
},{
    _id:{},
    a: 7,
    b: 8,
    c: 9
}]

And made an index on:

db.collection.ensureIndex({a:1,b:1,c:1})

The index would infact look something like: {1: [2,3]} with the first value being the left most field and the two others being the values that occur under that left most value.

Of course this is not how the index actually looks, I just did this to make it human readable to everyone. To find out how the index is actually formed you can watch some presentations, one I find good as a defacto to always watch is this one: http://www.mongodb.com/presentations/storage-engine-internals on storage internals.

So this means MongoDB works to select this index via a prefix method, whereby it will say that aand a,b are prefixes to the index and it can use those fields to fetch all the other values needed from the index.

Prefixing in this way means that the index will not work if you queried for:

db.collection.find({state:"NY",country:"YS"});
db.collection.find({state:"NY"});
db.collection.find({country:"YS"});

It is good to note that the order within the query DOES NOT MATTER. You can make the fields in the query any order you like, where it matter is IN THE INDEX.

Anyway that is a primer into why the queries will use that single index.