CODEWITHSUNDEEP
mongodbindexing
Mads
Mads

Reputation: 1206

Indexing with mongodb: bad performance / indexOnly=false

I have a mongodb on a 8GB linux machine running. Currently it's in test-mode so there are very few other requests coming in if any at all.

I have a colelction items with 1 million documents in it. I am creating an index on the fields: PeerGroup and CategoryIds (which is an array of 3-6 elements which will yield in an multi key): db.items.ensureIndex({PeerGroup:1, CategoryIds:1}.

When I am querying 

db.items.find({"CategoryIds" : new BinData(3,"xqScEqwPiEOjQg7tzs6PHA=="), "PeerGroup" : "anonymous"}).explain()

I have the following results:

{
    "cursor" : "BtreeCursor PeerGroup_1_CategoryIds_1",
    "isMultiKey" : true,
    "n" : 203944,
    "nscannedObjects" : 203944,
    "nscanned" : 203944,
    "nscannedObjectsAllPlans" : 203944,
    "nscannedAllPlans" : 203944,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 1,
    "nChunkSkips" : 0,
    "millis" : 680,
    "indexBounds" : {
            "PeerGroup" : [
                    [
                            "anonymous",
                            "anonymous"
                    ]
            ],
            "CategoryIds" : [
                    [
                            BinData(3,"BXzpwVQozECLaPkJy26t6Q=="),
                            BinData(3,"BXzpwVQozECLaPkJy26t6Q==")
                    ]
            ]
    },
    "server" : "db02:27017"

}

I think 680ms is not that very fast. Or is this acceptable? Also, why does it say "indexOnly:false" ?

Upvotes: 2

Views: 2840

Answers (1)

Adam Comerford
Adam Comerford

Reputation: 21682

I think 680ms is not that very fast. Or is this acceptable?

That kind of depends on how big these objects are and whether this was a first run. Assuming the whole data set (including the index) you are returning fits into memory, then they next time you run this it will be an in-memory query and will then return basically as fast as possible. The nscanned is high meaning that this query is not very selective, are most records going to have an "anonymous" value in PeerGroup? If so, and the CategoryId is more selective then you might try an index on {CategoryIds:1, PeerGroup:1} instead (use hint() to try out one versus the other).

Also, why does it say "indexOnly:false"

This simply indicates that all the fields you wish to return are not in the index, the BtreeCursor indicates that the index was used for the query (a BasicCursor would mean it had not). For this to be an indexOnly query, you would need to be returning only the two fields in the index (that is: {_id : 0, PeerGroup:1, CategoryIds:1}) in your projection. That would mean that it would never have to touch the data itself and could return everything you need from the index alone.

Upvotes: 5

Related Questions