CODEWITHSUNDEEP
mongodb
m.spyratos
m.spyratos

Reputation: 4229

MongoDB chooses the wrong index

I have a MongoDB collection called test with the following two simple documents:

    {
        "first_name" : "John",
        "last_name" : "Smith",
        "age" : 25
    }
    {
        "first_name" : "James",
        "last_name" : "Bond",
        "age" : 31
    }

I also created the following indexes for this collection:

    {
        "first_name" : 1
    }
    {
        "last_name" : 1
    }
    {
        "age" : 1
    }
    {
        "first_name" : 1,
        "last_name" : 1,
        "age" : 1
    }

The following queries use their corresponded field index:

db.getCollection('test').find({first_name: 'John'});
db.getCollection('test').find({last_name: 'Smith'});
db.getCollection('test').find({age: 25});

But when I perform the following query:

db.getCollection('test').find({first_name: 'John', last_name: 'Smith', age: 25});

It uses the single index for the field last_name, instead of the compound index that includes all three fields.

Screenshot of explain

Any ideas/references on this issue?
MongoDB version: 3.0.7

Thanks

Upvotes: 2

Views: 1126

Answers (2)

Hamed Lohi
Hamed Lohi

Reputation: 621

It seems that the order of defining the indexes is also effective in selecting it, for example :

{
    "first_name" : 1,
    "last_name" : 1,
    "age" : 1
}
{
    "first_name" : 1
}
{
    "last_name" : 1
}
{
    "age" : 1
}

also you can use Partial Indexes

Upvotes: 1

Roman
Roman

Reputation: 66196

MongoDB query planner decides which index is the most effective for a given query (also, starting from MongoDB 2.6 it can pick up more than one). Query planner documentation explanes it in detail.

If you want it to pick up your 3-keys compound index for the last query, you have two options:

  1. Make this decision result in faster query execution time. Basically, you need a few orders of magnitude bigger documents set size than just 3 documents.
  2. Use hint() on your cursor. You need to pass index name as a parameter of hint method, which you can find from collection.getIndexes() command output.

Upvotes: 4

Related Questions