Querying a Sub Object in MongoDB is not using the Index

I am recording site usage events in a sub object of a (visitor). here is a basic example of the data structure:

{ "_id" : ObjectId("4d4c695794b332a0740009bd"), "evs" : [
    {
            "ev" : "Visit Home Page",
            "d" : 1,
            "s" : 1
    },
    {
            "ev" : "Buy Product",
            "d" : "110.10",
            "upc" : 1234,
            "s" : 1
    },
    {
            "ev" : "Sign up to newsletter",
            "d" : "1",
            "s" : 1
    }
]}

I have an index on 'evs.s', but when I search on evs.s, the index is not used:

db.visitors.find({'evs.s':0}).explain()
{
    "cursor" : "BtreeCursor evs.s_1",
    "nscanned" : 33361,
    "nscannedObjects" : 33361,
    "n" : 33361,
    "millis" : 311,
    "nYields" : 105,
    "nChunkSkips" : 0,
    "isMultiKey" : false,
    "indexOnly" : false,
    "indexBounds" : {
            "evs.s" : [
                    [
                            0,
                            0
                    ]
            ]
    }
}

That query takes 311 milliseconds and scans through every object.

Here is the index: db.visitors.getIndexes()

{
  "ns" : "tracking.visitors",
  "unique" : false,
  "key" : {
     "evs.s" : 1
  },
  "name" : "evs.s_1",
  "v" : 0
}开发者_开发问答

Your query actually is using an index, as indicated by the cursor type in the explain output ("BtreeCursor evs.s_1"). If you were not using a an index, it would be "BasicCursor".

From your input data, it looks like evs.s might not be a very efficient key to index on. If all of the values of evs.s are either 1 or 0, your index will always hit a large number of matches.

My guess is that your query did not do a full table scan, but that there are actually that many records with a value of evs.s = 0 in your index.

You might compare the output of

db.visits.find({evs.s: 0}).count();

db.visits.find({evs.s: 1}).count();

db.visits.find().count();

to verify this.

There are several things you can do to speed this up:

1) You can use a different index that has more distinct values. This will reduce the search space on each query.

2) You can add a limit statement to your query. This will stop scanning the index once limit documents have been found.

"cursor" : "BtreeCursor evs.s_1"

means that the index is used.