CODEWITHSUNDEEP
mongodbgeospatialmongodb-indexes2dsphere
user805627
user805627

Reputation: 4417

Index prefix doesn't work if a compound index contains 2dsphere index

> db.test.ensureIndex({x: 1, location: '2dsphere'})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.test.find({x: 0}).explain()
{
        "cursor" : "BasicCursor",
        "isMultiKey" : false,
        "n" : 1,
        "nscannedObjects" : 100009,
        "nscanned" : 100009,
        "nscannedObjectsAllPlans" : 100009,
        "nscannedAllPlans" : 100009,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 781,
        "nChunkSkips" : 0,
        "millis" : 40,
        "server" : "hackintosh:27017",
        "filterSet" : false
}

MongoDB Version: 2.6.2

I created a compound index on x and location, but when I queried on x, why it didn't work?

Upvotes: 0

Views: 268

Answers (1)

Christian P
Christian P

Reputation: 12240

I'm guessing that your document doesn't have the location key or the location is null so your query will not use that index unless explicitly hinted.

That's probably because 2dsphere indexes in MongoDB 2.6 are sparse. You can use hint() to explicitly specify the index, but even then you will not find the documents without the location field because they will not be added to the index.

From the docs:

If a document lacks a 2dsphere index field (or the field is null or an empty array), MongoDB does not add an entry for the document to the 2dsphere index. For inserts, MongoDB inserts the document but does not add to the 2dsphere index.

For a compound index that includes a 2dsphere index key along with keys of other types, only the 2dsphere index field determines whether the index references a document.

If you really have to find documents by it's x field whether loc is set or not I would suggest adding a separate index (non-sparse index) just for that field.

Edit

I did some additional testing. In this case it seems that MongoDB will always default to using BasicCursor, unless you explicitly specify the index with a hint. Like Sammaye said, it's probably a known quirk.

Upvotes: 1

Related Questions