how does Joint index work in mongodb?

Question

In this question's comments, I know how to create index for sort operation: how does Mongodb index works?

1. But I want to know, when we create a joint index on a & b, how does it work different to a simple index?

2. And why will we benefit in just finding a, but if we find b, we do not get any benefit from it? Whether joint index is just like concatenating a & b, so we will get benefit from it for Prefix?

Answer 1

1.But I want to know ,when we create an joint index on 'a'&'b',how does it work difference with an simple index?

MongoDB only uses a single index per query .. so if your find() criteria includes both a and b values, you should add a compound index to efficiently search both fields.

2.And why we will benifit just find 'a' but if we find 'b' ,we will do not get any benifit from it? whether joint index just like concatenate 'a'&'b' so we will get benifit from it for Prefix？

MongoDB uses B-tree indexes, so you can only efficiently match partial keys using a prefix. To find all possible values matching a suffix or substring, all index entries would have to be checked.

Set up test data to compare

Examples below are using the mongo shell:

/* Generate some test data */
for (i = 0; i< 1000; i++) {
    db.mycoll.insert({a:i})
    db.mycoll.insert({b:i})
    db.mycoll.insert({a:i,b:i})
}

Now add some sample indexes:

/* Add simple and compound index */
db.mycoll.ensureIndex({a:1})
db.mycoll.ensureIndex({b:1})
db.mycoll.ensureIndex({a:1, b:1})

Finally, for the test scenarios below force your query to use a specific index with $hint and compare the explain() results.

Search for b using simple index

A search for b using the simple index on b can find matching entries directly in the index .. it scans 4 index entries (nscanned) to return 4 results (n):

db.mycoll.find({b:10}).hint({b:1}).explain()
{
    "cursor" : "BtreeCursor b_1",
    "n" : 4,
    "nscannedObjects" : 4,
    "nscanned" : 4,
    ...
}

Search for b using compound index (a,b)

A search for b using the compound index on (a,b) has to check every a value in the index, because the first part of the index is the key value of a.

So to find matching entries directly in the index .. it scans 1904 index entries (nscanned) to return 4 results (n):

db.mycoll.find({b:10}).hint({a:1,b:1}).explain()
{
    "cursor" : "BtreeCursor a_1_b_1",
    "n" : 4,
    "nscannedObjects" : 4,
    "nscanned" : 1904,
    ...
}

Technically scanning 1,904 documents is less than the 3,000 total in my test collection .. but this is far from optimal.

Search for a using compound index (a,b)

For comparison, a search of a using the compound index shows that only 4 values need to be scanned to return 4 documents:

db.mycoll.find({a:10}).hint({a:1,b:1}).explain()
{
    "cursor" : "BtreeCursor a_1_b_1",
    "n" : 4,
    "nscannedObjects" : 4,
    "nscanned" : 4,
    "nscannedObjectsAllPlans" : 4,
    ...
}

For some further examples and explanation, I would recommend reading the article Optimizing MongoDB Compound Indexes.