MongoDB covering index not working

Question

I have a countries document which looks like that:

{
  "_id" : ObjectId("4e493af4140700590800154f"),
  "geoname_id" : "49518",
  "code" : "rw",
  "names" : {
    "en" : "Rwanda",
    "nl" : "Rwanda",
    "de" : "Ruanda"
  }
}

In order to touch the index only when querying for:

db.countries.find({}, {"names.en":1, _id:0})

I added the following index:

db.countries.ensureIndex({"names.en":1})

To my understanding the query should now only touch the index. However, .explain() tells me that the query isnt using any index at all:

{
  "cursor" : "BasicCursor",
  "nscanned" : 247,
  "nscannedObjects" : 247,
  "n" : 247,
  "millis" : 0,
  "nYields" : 0,
  "nChunkSkips" : 0,
  "isMultiKey" : false,
  "indexOnly" : false,
  "indexBounds" : {
  }
}

I thought that the reason might be that the full db will be output (247 countries) but that doesnt make any sense to me. When the countries are available within the index the index should be used, right?

Anybody has an idea?

Cheers

Remon van Vliet · Accepted Answer · 2011-08-16T09:06:05.580

2

The reason it's not using an index is because you're not querying on any criteria. Without find criteria the query optimizer will not select an index to use and therefor cannot function as a covered index.

Try db.countries.find({"names.en":"Rwanda"}, {"names.en":1, _id:0}).explain() to verify that with an indexed criteria it will in fact hit the appropriate index.

A case can be made for MongoDB needing to be smart enough to realize it can use the index to satisfy the original query, but currently it does not. You can use .sort({"names.en":1}) to make it select the index as well.

edited Aug 16 '11 at 09:06

answered Aug 16 '11 at 08:36

Remon van Vliet

18,365
3
52
57

So, to be totally clear: there is no way to get the complete list of countries from the index only unless I would restructure the document in a way that I can query by a criteria, say "language:"en" ? – fightbulc Aug 16 '11 at 08:49
Not sure if restructuring is needed since {"names.en":{$ne:"None"}} as criteria would hit the same list as {}. But yes, you need criteria before it hits an index at the moment. – Remon van Vliet Aug 16 '11 at 08:59
No problem. Note that using .sort() also makes it use an index. – Remon van Vliet Aug 16 '11 at 09:05
Also note that MongoDB currently won't use a covered index when one of the output fields is a dotted path to a field in a sub-document. You can watch and vote on https://jira.mongodb.org/browse/SERVER-2104 to track this issue. – dcrosta Aug 16 '11 at 11:49
@dcrosta: true, I was noticing that while playing around with indexes to see when "indexOnly" hits "true". Thanks for the input. I am used to MySQL's Indexing and simply expected mongodb to behave alike. – fightbulc Aug 16 '11 at 13:08

score 0 · Answer 2 · edited Feb 18 '13 at 21:31

0

As of now, you can hint Mongo to use the index you want (ref):

db.countries.find({}, {"names.en":1, _id:0}).hint({"names.en":1})

I'm not sure when this feature was added, though.

edited Feb 18 '13 at 21:31

eldarerathis

35,455
10
90
93

answered Feb 18 '13 at 21:21

nexuzzz

238
3
7

score 0 · Answer 3 · answered Apr 28 '13 at 13:22

0

Using a sort will ensure that it will use the index, FOR SORTING only.

Treat the "find" and the "sort" as two separated operations.

answered Apr 28 '13 at 13:22

Luis Ferro

95
2

MongoDB covering index not working

3 Answers3