Simple MongoDB query very slow although index is set

Question

I've got a MongoDB collection that holds about 100M documents.

The documents basically look like this:

_id             : ObjectId("asd1234567890")
_reference_1_id : ObjectId("fgh4567890123")
_reference_2_id : ObjectId("jkl7890123456")
name            : "Test1"
id              : "4815162342"
created_time    : Date( 1331882436000 )
_contexts       : ["context1", "context2"]
...

There are some indexes set, here's the output of db.mycoll.getIndexes();

[
{
    "v" : 1,
    "key" : {
        "_id" : 1
    },
    "ns" : "mydb.mycoll",
    "name" : "_id_"
},
{
    "v" : 1,
    "key" : {
        "_reference_1_id" : 1,
        "_reference_2_id" : 1,
        "id" : 1
    },
    "unique" : true,
    "ns" : "mydb.mycoll",
    "name" : "_reference_1_id_1__reference_2_id_1_id_1"
},
{
    "v" : 1,
    "key" : {
        "_reference_1_id" : 1,
        "_reference_2_id" : 1,
        "_contexts" : 1,
        "created_time" : 1
    },
    "ns" : "mydb.mycoll",
    "name" : "_reference_1_id_1__reference_2_id_1__contexts_1_created_time_1"
}
]

When I execute a query like

db.mycoll.find({"_reference_2_id" : ObjectId("jkl7890123456")})

it takes over an hour (!) until it's finished, no matter if there are results or not. Any ideas?

Update: Here's what the output of

db.mycoll.find({"_reference_2_id" : ObjectId("jkl7890123456")}).explain();

looks like:

{
"cursor" : "BasicCursor",
"nscanned" : 99209163,
"nscannedObjects" : 99209163,
"n" : 5007,
"millis" : 5705175,
"nYields" : 17389,
"nChunkSkips" : 0,
"isMultiKey" : false,
"indexOnly" : false,
"indexBounds" : {

}
}

AD7six · Accepted Answer · 2012-03-20T11:58:02.133

You don't have any index that mongo will automatically use for that, so it's doing a full table scan.

As mentioned in the docs

If the first key [of the index] is not present in the query, the index will only be used if hinted explicitly.

Why

If you have an index on a,b - and you search by a alone - an index will automatically be used. This is because it's the start of the index (which is fast to do), the db can just ignore the rest of the index value.

An index on a,b is inefficient when searching by b alone simply because it doesn't give the possibility to use the index searching with "starts with thisfixedstring".

So, either:

Include _reference_1_id in the query (probably irrelevant)
OR add an index on _reference_2_id (if you query by the field often)
OR use a hint

Hint

Probably your lowest-cost option right now.

Add a query hint to force using your _reference_1_id_1__reference_2_id_1_id_1 index. Which is likely to be a lot faster than a full table scan, but still a lot slower than an index which starts with the field you are using in the query.

i.e.

db.mycoll
    .find({"_reference_2_id" : ObjectId("jkl7890123456")})
    .hint("_reference_1_id_1__reference_2_id_1_id_1");

Perfect, thanks a million for your detailed answer! I can actually include the _reference_1_id so I didn't even need to hint to the index. — trautwein, Mar 16 '12 at 11:40

score 1 · Answer 2 · answered Apr 05 '12 at 17:20

Hye, I've quiet the same problem on an equivalent amount of datas. In the documentation, it's written that queries with index must fit in ram. I think this is not the case, the query must be doing a lot of disk access to first retrieve the index and then get the value. In your case, a direct collection read will be faster.

EV.

score 0 · Answer 3 · answered Mar 16 '12 at 10:07

0

I would try setting a non-unique index on _reference_2_id, because at the moment, I suspect you'll be doing the equivalent of a full table scan as even though the indexes contain _reference_2_id, they won't be used (see here).

answered Mar 16 '12 at 10:07

beny23

34,390
5
82
85

Looking at the output of explain() (see updated question), you're right about that I'm doing a full table scan without the index being used. But why would that be case? I can't see any information on that in the MongoDB docs you [linked](http://www.mongodb.org/display/DOCS/Indexes#Indexes-CompoundKeys) to. – trautwein Mar 16 '12 at 11:04

Simple MongoDB query very slow although index is set

3 Answers3

Linked