Mongo DB - Slower With Index

Question

I have about 1000000 documents in a collections (random generated).

Sample document:

{
    "loc": {
        "lat": 39.828475,
        "lon": 116.273542
    },
    "phone": "",
    "keywords": [
        "big",
        "small",
        "biggest",
        "smallest"
    ],
    "prices": [
        {
            "category": "uRgpiamOVTEQ",
            "list": [
                {
                    "price": 29,
                    "name": "ehLYoPpntlil"
                }
            ]
        },
        {
            "category": "ozQNmdwpwhXPnabZ",
            "list": [
                {
                    "price": 96,
                    "name": "ITTaLHf"
                },
                {
                    "price": 88,
                    "name": "MXVgJFBgtwLYk"
                }
            ]
        },
        {
            "category": "EDkfKGZSou",
            "list": [
                {
                    "price": 86,
                    "name": "JluoCLnenOqDllaEX"
                },
                {
                    "price": 35,
                    "name": "HbmgMDfxCOk"
                },
                {
                    "price": 164,
                    "name": "BlrUD"
                },
                {
                    "price": 106,
                    "name": "LOUcsMDeaqVm"
                },
                {
                    "price": 14,
                    "name": "rDkwhN"
                }
            ]
        }
    ],
}

Search without indexes

> db.test1.find({"prices.list.price": { $gt: 190 } }).explain()
{
    "cursor" : "BasicCursor",
    "isMultiKey" : false,
    "n" : 541098,
    "nscannedObjects" : 1005584,
    "nscanned" : 1005584,
    "nscannedObjectsAllPlans" : 1005584,
    "nscannedAllPlans" : 1005584,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 8115,
    "nChunkSkips" : 0,
    **"millis" : 13803,**
    "server" : "localhost:27017",
    "filterSet" : false
}

With indexes:

> db.test1.ensureIndex({"prices.list.price":1,"menu.list.name":1})
{
    "createdCollectionAutomatically" : false,
    "numIndexesBefore" : 1,
    "numIndexesAfter" : 2,
    "ok" : 1
}

> db.test1.find({"prices.list.price": { $gt: 190 } }).explain()
{
    "cursor" : "BtreeCursor prices.list.price_1_prices.list.name_1",
    "isMultiKey" : true,
    "n" : 541098,
    "nscannedObjects" : 541098,
    "nscanned" : 868547,
    "nscannedObjectsAllPlans" : 541098,
    "nscannedAllPlans" : 868547,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 16852,
    "nChunkSkips" : 0,
    **"millis" : 66227,**
    "indexBounds" : {
        "menu.list.price" : [
            [
                190,
                Infinity
            ]
        ],
        "menu.list.name" : [
            [
                {
                    "$minElement" : 1
                },
                {
                    "$maxElement" : 1
                }
            ]
        ]
    },
    "server" : "localhost:27017",
    "filterSet" : false
}

Have any idea why indexed search slower than without index ?

Also i will use:

db.test1.find( { loc : { $near : [39.876045, 32.862245]}}) (need 2d indexes)

db.test1.find({ keywords:{$in: [ "small", "another" ] }}) (use index for keywords)

db.test1.find({"prices.list.name":/.s./ }) (no need to index because i will use regex)

Answer 1

An index allows faster access to location of the document that satisfies the query.

In your example, your query selects half of all the documents in the collection. So even though the index scan provides faster access to know which documents will match the query predicate, it actually creates a lot more work overall.

In collection scan, the query is scanning all of the documents, and checking the field that you are querying by to see if it matches. Half the time it ends up selecting the document.

In index scan, the query is traversing half of all the index entries and then jumping from them directly to the documents that satisfy the query predicate. That's more operations in your case.

In addition, while doing this, the operations are yielding the read mutex when they need to wait for the document they have to read to be brought into RAM, or when there is a write that is waiting to go, and the index scan is showing double the number of yields as the collection scan. If you don't have enough RAM for your working set, then adding an index will put more pressure on the existing resources and make things slower, rather than faster.

Try the same query with price compared to a much larger number, like 500 (or whatever would be a lot more selective in your data set). If the query is still slower with an index, then you are likely seeing a lot of page faulting on the system. But if there is enough RAM for the index, then the indexed query will be a lot faster while the unindexed query will be just as slow.

Answer 2

First, as a suggestion you will get more faster while querying arrays with elemMatch. http://docs.mongodb.org/manual/reference/operator/query/elemMatch/ In your case

db.test1.find({"prices.list.price":{ $elemMatch: { $gte: 190 }}  })

Second thing is

To index a field that holds an array value, MongoDB adds index items for each item in the array. These multikey indexes allow MongoDB to return documents from queries using the value of an array. MongoDB automatically determines whether to create a multikey index if the indexed field contains an array value; you do not need to explicitly specify the multikey type.

Consider the following illustration of a multikey index:

Diagram of a multikey index on the addr.zip field. The addr field contains an array of address documents. The address documents contain the zip field.

Multikey indexes support all operations supported by other MongoDB indexes; however, applications may use multikey indexes to select documents based on ranges of values for the value of an array. Multikey indexes support arrays that hold both values (e.g. strings, numbers) and nested documents.

from http://docs.mongodb.org/manual/core/index-multikey/