Why multiKey indexes blocks indexOnly queries in MongoDB?

Question

I am trying to use only the index while reading data from a collection in MongoDB, because I have some big documents, while for this query I need only one field.

It turns out that I cannot have indexOnly = true if the index is a multiKey index.

Here is the test I made:

db.test.drop()
db.test.insert({a:1})
db.test.ensureIndex({a:1})
db.test.find({a:1}, {_id:0, a:1}).explain()

-> indexOnly = true, isMultiKey = false

db.test.insert({a : [2,3]})
db.test.find({a:1}, {_id:0, a:1}).explain()

-> indexOnly = false, isMultiKey = true

The documentation mentions some limitations of the multikey indexes, but not this one. Does anybody have an idea how to use both multikey and indexonly?

There's an open issue that aims to fix that, if you wish to project non-multikey fields: jira.mongodb.org/browse/SERVER-3173 — salty-horse
– salty-horse, Commented May 11, 2015 at 14:27

jbub · Accepted Answer · 2013-11-17 19:36:23Z

6

From: http://docs.mongodb.org/manual/tutorial/create-indexes-to-support-queries/#create-indexes-that-support-covered-queries

An index cannot cover a query if any of the indexed fields in any of the documents in the collection includes an array. If an indexed field is an array, the index becomes a multi-key index index and cannot support a covered query.

You are inserting an array into your test collection, so when mongo is creating the index, it has to create a MultiKey index (it means it is creating the index for each item of the array).

answered Nov 17, 2013 at 19:36

jbub

2,66526 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Asya Kamsky Over a year ago

i think the question was why there is such a limitation.

Slavcho Ivanov Over a year ago

Thanks. That answers it. I will probably create another collection as a "custom index" to minimize disk reads. I hoped mongo can do this for me :).

Asya Kamsky · Accepted Answer · 2013-11-17 19:37:02Z

2

Note that when you have an indexOnly query, the returned document must be synthesized out of the information in the index only. That means that without looking at the document, the correct exact document must be returned.

In case of multiKey index, the query doesn't know whether to return
{ "a" : 1 }
or
{ "a" : [ 1 ] }

All it can tell is that "a" has value 1 and index is of type multiKey. It needs to look in the document to tell whether the type of "a" is an array or number.

answered Nov 17, 2013 at 19:37

Asya Kamsky

42.4k5 gold badges113 silver badges133 bronze badges

5 Comments

Sammaye Over a year ago

So it isn't something that will be worked around in later versions? since if there is multiple values for a field in the index for a single _id it is safe to assume that it should be returning an array. Edit: actually I know the full problem now

Asya Kamsky Over a year ago

if there is multiple values, then yes. if there is one, then we don't know. but index is traversed by values, not by document so the order is wrong to use this information, much simpler to look at the document.

Sammaye Over a year ago

Yeah that last bit was what went through my mind after posting the comment :)

Slavcho Ivanov Over a year ago

That was a good suggestion. However I just made that test: db.test.drop() db.test.insert({a:[2,1], b:1}) db.test.ensureIndex({a:1, b:1}) db.test.find({a:1}, {_id:0, b:1}).explain() still -> multi = true, index only = false

Asya Kamsky Over a year ago

this is because of you are searching on a and returning b. the value for b is not in the index that's only one a!

Collectives™ on Stack Overflow

Why multiKey indexes blocks indexOnly queries in MongoDB?

2 Answers 2

2 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related