MongoDB Index

Add, Find or Drop a MongoDB index

Add a new index for field name

> db.stores.ensureIndex({name:1})
> db.system.indexes.find( { ns: "commerce.stores" } )
{ "name" : "_id_", "ns" : "commerce.stores", "key" : { "_id" : 1 }, "v" : 0 }
{ "_id" : ObjectId("4db8583c4f0b495c3a7dbb84"), "ns" : "commerce.stores",
"key" : { "name" : 1 }, "name" : "name_1", "v" : 0 }
  • MongoDB index is case sensitive in matching documents

Adding MongoDB index is a blocking operation by default. To change to background operation, add the background field with other index options

db.stores.ensureIndex({name:1}, {background:true, unique:true});

Find all or individual MongoDB index

db.stores.getIndexes()

db.system.indexes.find( { ns: "commerce.stores" } )

Drop an MongoDB index

db.stores.dropIndex({name:1})

Drop all MongoDB indexes on a collection

db.stores.dropIndexes()

Find the total size of an index.

db.stores.totalIndexSize();

MongoDB Compound Index

Adding MongoDB compound index for field (name, location)

db.stores.ensureIndex({name:1, location:-1})
  • -1 means in descending order

For a query that selects on multiple keys, a compound-key index is necessary (Rather than multiple indexes for each key)

MongoDB Document Index

Use document key as index

db.stores.insert( { name: "Dave Jones", location: { city: "San Francisco", state: "CA" } } )
db.stores.ensureIndex( { location : 1 } )

db.stores.find( { location: { city: "San Francisco", state: "CA" } } )
{ "_id" : ObjectId("4db88a024f0b495c3a7dbb91"), "name" : "Dave Jones",
"location" : { "city" : "San Francisco", "state" : "CA" } }

Index is order sensitive. The following query cannot make use of an index

db.stores.find( { location: { state: "CA" , city: "San Francisco" } } );

Index a hierarchy document

db.stores.ensureIndex( {"location.city":1})

Define multi-key documents for search

> o1 = {
    name : "Dave Jones",
    props : [
          { age : 20 },
          { occupation : 'engineer' },
          { city : 'San Francisco' }
    };
> db.stores.insert(o1);
> db.stores.ensureIndex({props:1});
> db.stores.find( { props : {age:20} } );                // uses index
> db.stores.find( { props : {city:'San Francisco'} } );  // uses index

MongoDB Spare Index

Spare index will contain documents that have the specific field

db.people.ensureIndex({name : 1}, {sparse : true})
  • find and filter operation will not return documents that does not have the specific field if spare index is used
  • Spare index currently only support 1 single field

MongoDB Unique Index

Enforce fields (name, age) must be unique in the document

db.stores.ensureIndex({name: 1, age: 1}, {unique: true});

MongoDB Re-index

Re-index the field

db.stores.reIndex()
  • Note this is a blocking operation and can be slow

MongoDB Geospatial Indexing

Create a 2-dimensional geospatial indexing

o1 = { position : { x : 20 , y : 30 } }
o2 = { position : { long : 41.2, lat: 70.2 } }
db.store.save(o1)
db.store.save(o2)

db.stores.ensureIndex( { position : "2d" } )
  • Only 1 spatial index per collection

Over default value for the Geospatial indexing

db.stores.ensureIndex( { position : "2d" } , { min : -100 , max : 100,  bits : 26 } )
  • min & max override the default min and max range for the field (-180,180)
  • bits define the precision used

MongoDB query on Spatial Data

Match exactly with a location

db.stores.find( { position : [40,70] } )

Return results from the nearest to $near first

db.stores.find( { position : { $near : [40,70] } } )
db.stores.find( { position : { $nearSphere : [40,70] } } )

db.stores.find( { position : { $near : [40,70] }, type : "retail" } )
  • nearSphere is more accurate by taking into account that the earth is curve

Limit the amount of returned documents

db.stores.find( { position : { $near : [40,70] } } ).limit(20)

Do not search beyond certain distances

db.stores.find( { position : { $near : [40,70] , $maxDistance : 4 } } ).limit(20)

For maxDistance 1° of latitude is about 69.11 miles

geoNear command

db.runCommand({geoNear:"stores", near:[40,70], spherical : true, num : 10})
db.runCommand({geoNear:"stores", near:[40,70], num : 10})
{
        "ns" : "commerce.stores",
        "near" : "1100011110100001011110100001011110100001011110100001",
        "results" : [
                {
                        "dis" : 1.2165516398343401,
                        "obj" : {
                                "_id" : ObjectId("4db897c04f0b495c3a7dbb99"),
                                "position" : {
                                        "long" : 41.2,
                                        "lat" : 70.2
                                }
                        }
                },
                {
                        "dis" : 3.9408108364155554,
                        "obj" : {
                                "_id" : ObjectId("4db899bd4f0b495c3a7dbb9e"),
                                "position" : {
                                        "long" : 43.2,
                                        "lat" : 72.3
                                }
                        }
                }
        ],
        "stats" : {
                "time" : 0,
                "btreelocs" : 1,
                "nscanned" : 2,
                "objectsLoaded" : 2,
                "avgDistance" : 2.5786812381249478,
                "maxDistance" : 3.9408108364155554
        },
        "ok" : 1
}
  • Produce statistics and distance information

Use a more accurate spherical calculation in MongoDB spatial search

db.runCommand({geoNear:"stores", near:[40,70], spherical : true, num : 10})

Add more query parameters

db.runCommand({geoNear:"stores", near:[40,70], num : 10}, query : { type : "retail" })

MongoDB Spatial Search with Bounding Box

Search by bounding box is faster but results will not be sorted by distance like "near"

box = [[40, -71], [41,  -70]]
db.stores.find({"position" : {"$within" : {"$box" : box}}})

Spatial search by a radius in MongoDB

center = [40, -70]
radius = 1
db.stores.find({"position" : {"$within" : {"$center" : [center, radius]}}})

Use $centerSphere and $boxSphere for a more accurate spherical calculation

Supporting multiple location document

db.stores.insert({ addresses : [ { name : "Home", position : [30, 40] }, { name : "Work", position : [31, 41] } ] })
db.stores.ensureIndex({ "addresses.position" : "2d" })

Multi-key Indexing

Index a field containing an array of values

db.stores.save( { name: "Dave Jones",  hobbies: ['music', 'sport'] } )
db.stores.ensureIndex( { hobbies : 1 } )

db.stores.find( { hobbies: 'music' } )

For compound index, only one index field can contain an array

An exact match

db.stores.find( { hobbies: { $all: [ 'music', 'sport' ] } } )

Add or remove a key to an array

db.stores.update({name: "Dave Jones"},{$addToSet:{hobbies:"fishing"}})

db.stores.update({name: "Dave Jones"},{$pull:{tags:"fishing"}})

Explain execution plan

Explain how MongoDB execute the query

db.stores.find({ name : 'Dave Jones'}).explain()
{
        "cursor" : "BtreeCursor name_1",
        "nscanned" : 5,
        "nscannedObjects" : 5,
        "n" : 5,
        "millis" : 17,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {
                "name" : [
                        [
                                "Dave Jones",
                                "Dave Jones"
                        ]
                ]
        }
}
  • cursor BtreeCursor indicates index is used
  • nscanned: number of documents scanned
  • n: number of documentes returned
  • millis: execution time
  • nscannedObjects: number of objects examined
  • nYields: number of times the query yielded the read lock to let writes
  • indexOnly - if the query could be resolved by an index

MongoDB optimizer tries different query plans and learn which ones work better. MongoDB executes multiple query plans in parallel. As soon as one finishes, it terminates the other executions, and the system learned which plan is good.

Use MongoDB Hint

Force the use of MongDB index with hint

db.stores.find({a:1,b:2,c:6}).hint({a:1,b:1});

Force not to use index

db.stores.find().hint({$natural:1})