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: https://jira.mongodb.org/browse/SERVER-3173 — salty-horse, May 11 '15 at 14:27

score 6 · Accepted Answer · answered Nov 17 '13 at 19:36

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 '13 at 19:36

jbub

2,586
23
23

i think the question was *why* there is such a limitation. – Asya Kamsky Nov 17 '13 at 19:37
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 :). – Slavcho Ivanov Nov 18 '13 at 09:24

score 2 · Answer 2 · answered Nov 17 '13 at 19:37

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 '13 at 19:37

Asya Kamsky

41,784
5
109
133

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 – Sammaye Nov 17 '13 at 19:43
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. – Asya Kamsky Nov 17 '13 at 19:45
Yeah that last bit was what went through my mind after posting the comment :) – Sammaye Nov 17 '13 at 19:53
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 – Slavcho Ivanov Nov 18 '13 at 09:19
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! – Asya Kamsky Nov 19 '13 at 23:32

Why multiKey indexes blocks indexOnly queries in MongoDB?

2 Answers2