how does Mongodb index works?

Question

I have a collection such as:
{u'_id': ObjectId('5094cc44e3f0f827b3618918'),
  u'xxx': 0},
 {u'_id': ObjectId('5094cc44e3f0f827b3618919'),
  u'xxx': 1},
 {u'_id': ObjectId('5094cc44e3f0f827b361891a'),
  u'xxx': 2},
 {u'_id': ObjectId('5094cc44e3f0f827b361891b'),
  u'xxx': 3},
 {u'_id': ObjectId('5094cc44e3f0f827b361891c'),
  u'xxx': 4}
...

when I create index such as:

db.test.ensure_index([("_id",-1),("xxx",1)])
db.test.ensure_index([("xxx",1)])

then,I use the explain such as:

db.test.find({"xxx":1}).sort("_id",-1).skip(5).limit(5).explain()

result is:
{u'allPlans': [{u'cursor': u'BtreeCursor _id_ reverse',
                u'indexBounds': {u'_id': [[{u'$maxElement': 1},
                                           {u'$minElement': 1}]]},
                u'n': 9,
                u'nscanned': 34,
               u'nscannedObjects': 34},
               {u'cursor': u'BtreeCursor xxx_1',
                u'indexBounds': {u'xxx': [[1, 1]]},
                u'n': 34,
                u'nscanned': 34,
                u'nscannedObjects': 34},
               {u'cursor': u'BtreeCursor _id_-1_xxx_1',
                u'indexBounds': {u'_id': [[{u'$maxElement': 1},
                                           {u'$minElement': 1}]],
                                 u'xxx': [[1, 1]]},
                u'n': 10,
                u'nscanned': 38,
                u'nscannedObjects': 10},
               {u'cursor': u'BasicCursor',
                u'indexBounds': {},
                u'n': 16,
                u'nscanned': 34,
                u'nscannedObjects': 34}],
 u'cursor': u'BtreeCursor xxx_1',
 u'indexBounds': {u'xxx': [[1, 1]]},
 u'indexOnly': False,
 u'isMultiKey': False,
 u'millis': 1,
 u'n': 5,
 u'nChunkSkips': 0,
 u'nYields': 0,
 u'nscanned': 34,
 u'nscannedAllPlans': 140,
 u'nscannedObjects': 34,
 u'nscannedObjectsAllPlans': 112,
 u'scanAndOrder': True,
 u'server': u'ubuntu:27017'}

from n,nscanned and nscnnedObjects 's num,I think It should use u'BtreeCursor id-1_xxx_1' as cursor,but Why It use u'cursor': u'BtreeCursor xxx_1',? Can anyone give me some suggestion ?I have a little knowledge about the index optimize.

Answer 1

The order of fields in the index matters; the best compound index for your find and sort example would actually be:

db.test.ensure_index([("xxx",1),("_id",-1)])

Since your search criteria is on field 'xxx', putting this field first in the index will find more results than searching by _id and then filtering to documents matching your xxx criteria.

If you look at the n number for each plan considered by the query optimizer in allPlans, the BtreeCursor xxx_1 index actually returns the most results (34). The other indexes return 9, 10, and 16 results .. so would be less efficient for the given search criteria.

For more information on index optimization, this article is very helpful: Optimizing MongoDB Compound Indexes.