Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can a Compound Index serve multiple queries

Tags:

mongodb

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})
like image 611
Pawan Avatar asked Sep 09 '13 12:09

Pawan


People also ask

What is special about a compound index?

A compound index is an index that contains references to multiple fields within a document. The compound indexes have some interesting properties. The index is usable if you have a query that includes nationality, age and name. But it's also able to answer other queries using the index.

How do compound indexes work?

How does composite index work? The columns used in composite indices are concatenated together, and those concatenated keys are stored in sorted order using a B+ Tree. When you perform a search, concatenation of your search keys is matched against those of the composite index.

What is the use of compound index in MongoDB?

MongoDB supports compound indexes, where a single index structure holds references to multiple fields [1] within a collection's documents. The following diagram illustrates an example of a compound index on two fields: MongoDB imposes a limit of 32 fields for any compound index.

Can a query use multiple indexes?

Yes, MySQL can use multiple index for a single query. The optimizer will determine which indexes will benefit the query. You can use EXPLAIN to obtain information about how MySQL executes a statement.


1 Answers

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 a and 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.

like image 163
Sammaye Avatar answered Sep 29 '22 20:09

Sammaye