Why is my mongo query not using index only?

Question

Please, observe:

MongoDB shell version: 2.4.1
connecting to: test
> use dummy
switched to db dummy
> db.invoices.find({'items.nameTags': /^z/}, {_id: 1}).explain()
{
        "cursor" : "BtreeCursor items.nameTags_1_created_1_special_1__id_1_items.qty_1_items.total_1 multi",
        "isMultiKey" : true,
        "n" : 55849,
        "nscannedObjects" : 223568,
        "nscanned" : 223568,
        "nscannedObjectsAllPlans" : 223568,
        "nscannedAllPlans" : 223568,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 86,
        "nChunkSkips" : 0,
        "millis" : 88864,
        "indexBounds" : {
                "items.nameTags" : [
                        [
                                "z",
                                "{"
                        ],
                        [
                                /^z/,
                                /^z/
                        ]
                ],
                "created" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ],
                "special" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ],
                "_id" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ],
                "items.qty" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ],
                "items.total" : [
                        [
                                {
                                        "$minElement" : 1
                                },
                                {
                                        "$maxElement" : 1
                                }
                        ]
                ]
        },
        "server" : "IL-Mark-LT:27017"
}
>

Here is the definition of the index:

> db.system.indexes.find({name : 'items.nameTags_1_created_1_special_1__id_1_items.qty_1_items.total_1'}).pretty()
{
        "v" : 1,
        "key" : {
                "items.nameTags" : 1,
                "created" : 1,
                "special" : 1,
                "_id" : 1,
                "items.qty" : 1,
                "items.total" : 1
        },
        "ns" : "dummy.invoices",
        "name" : "items.nameTags_1_created_1_special_1__id_1_items.qty_1_items.total_1"
}
>

Finally, here is an example invoice document (with just 2 items):

> db.invoices.findOne({itemCount: 2})
{
        "_id" : "85923",
        "customer" : "Wgtd Fm 91",
        "businessNo" : "314227928",
        "billTo_name" : "Wgtd Fm 91",
        "billTo_addressLine1" : "3839 Ross Street",
        "billTo_addressLine2" : "Kingston, ON",
        "billTo_postalCode" : "K7L 4V4",
        "purchaseOrderNo" : "boi",
        "terms" : "COD",
        "shipDate" : "2013-07-10",
        "shipVia" : "Moses Transportation Inc.",
        "rep" : "Snowhite",
        "items" : [
                {
                        "qty" : 4,
                        "name" : "CA 7789",
                        "desc" : "3 pc. Coffee Table set (Silver)",
                        "price" : 222.3,
                        "total" : 889.2,
                        "nameTags" : [
                                "ca 7789",
                                "a 7789",
                                " 7789",
                                "7789",
                                "789",
                                "89",
                                "9"
                        ],
                        "descTags" : [
                                "3",
                                "pc",
                                "c",
                                "coffee",
                                "offee",
                                "ffee",
                                "fee",
                                "ee",
                                "e",
                                "table",
                                "able",
                                "ble",
                                "le",
                                "e",
                                "set",
                                "et",
                                "t",
                                "silver",
                                "ilver",
                                "lver",
                                "ver",
                                "er",
                                "r"
                        ]
                },
                {
                        "qty" : 4,
                        "name" : "QP 8681",
                        "desc" : "Ottoman Bed",
                        "price" : 1179.1,
                        "total" : 4716.4,
                        "nameTags" : [
                                "qp 8681",
                                "p 8681",
                                " 8681",
                                "8681",
                                "681",
                                "81",
                                "1"
                        ],
                        "descTags" : [
                                "ottoman",
                                "ttoman",
                                "toman",
                                "oman",
                                "man",
                                "an",
                                "n",
                                "bed",
                                "ed",
                                "d"
                        ]
                }
        ],
        "itemCount" : 2,
        "discount" : "10%",
        "delivery" : 250,
        "hstPercents" : 13,
        "subTotal" : 5605.6,
        "totalBeforeHST" : 5295.04,
        "total" : 5983.4,
        "totalDiscount" : 560.56,
        "hst" : 688.36,
        "modified" : "2012-10-08",
        "created" : "2014-06-25",
        "version" : 0
}
>

My problem is that mongodb does not use index only according to the aforementioned explain() output. Why? After all I only request the _id field, which is part of the index.

In general, I feel that I am doing something very wrong. My invoices collection has 65,000 invoices with the total of 3,291,092 items. It took almost 89 seconds to explain() the query.

What am I doing wrong?

Answer 1

You are using arrays and subdocuments. Covered Indexes dont work with either of these.

From the mongo docs:

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.
• any of the indexed fields are fields in subdocuments. To index fields in subdocuments, use dot notation. For example, consider a collection users with documents of the following form:

http://docs.mongodb.org/manual/tutorial/create-indexes-to-support-queries/