MongoDB Indexes

mongodb_indexes.md

#MongoDB Indexes There are 5 different index types supported in MongoDB.

1. Regular (B-Tree) - supports indexes on a single or multiple fields
2. Geo - supports indexing on a geographical location (useful for finding locations nearby)
3. Text - Allows for search engine like full text searching
4. Hashed - This is used primarily used for sharding as it evenly distributes the hashes rather than clustering them.
5. Time to Life - This supports expiring based on a document date field

##Create Index

db.animals.ensureIndex(keys, options)  //animals is the collection

The keys refers to which fields and in what order and if it is a Geo or Text index. The options specify the Name (max 128 chars), if you want to build it now or in the background, Should it be a unique index, sparse (ie exclude documents where the field is missing), TTL and language.

##Find Indexes You can find out what indexes exist by querying the system.indexes.

db.system.indexes.find({ns:'test.animals'}, {key:1})  //test is the db, animals is the collection, ns stands for namespace

The result is that there is one index on the _id field.

{ "key" : { "_id" : 1 } }

##.explain() Just because an index exists does not mean that it is being used. To check to see if an index is being used you need to use .explain(). You simply select the query you want to check:

db.animals.find({name: 'cat'}).explain()

Instead of the data it returns the details of what it did.

{
	"cursor" : "BasicCursor",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 5,
	"nscanned" : 5,
	"nscannedObjectsAllPlans" : 5,
	"nscannedAllPlans" : 5,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"server" : "NDX0001:27017",
	"filterSet" : false
}

The clue here is that the cursor was a BasicCursor. If this was using an index it would say it.

###Build the index. This will create an index on name in ascending order. Note 1 means ascending -1 means decending. You can create indexes on multiple fields too up to a max or 31 fields.

db.animals.ensureIndex({name:1})

{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}

###Check the indexes

db.system.indexes.find({ns:'test.animals'}, {key:1})

{ "key" : { "_id" : 1 } }
{ "key" : { "name" : 1 } }

###Check the query .explain() again

{
	"cursor" : "BtreeCursor name_1",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"name" : [
			[
				"cat",
				"cat"
			]
		]
	},
	"server" : "NDX0001:27017",
	"filterSet" : false
}

Here we can see that it is now using a BTree index. We did not give a name to the index so it shows as name_1. You can also see that the nscanned documents is 1. Previously it was 5 so this shows that it did not need to scan the entire collection to get the result.

##Deleting an Index You can delete an index using:

db.animals.dropIndex("name_1")

Here name_1 is the default name that was used when the index was created but you could also have specified a name when the index was being created. Note you cannot delete the index on the _id field.

##Index on Nested Field You can also create an index on a nested field using:

db.animals.ensureIndex({"info.type":1})

##Index on Array You can also index on an array. This will index on all the elements of the array for all documents.

db.animals.ensureIndex({tags:1})

##Unique Indexes You can create a unique index using:

db.animals.ensureIndex({name:1},{unique:true})

If a user tries to add a new animal with a name that is already in the database this will throw an error.

##Sparse Index A sparse index will only create an entry for documents that have the field. This reduces the size of the index if the field is only present in some of the documents.

db.animals.ensureIndex({"info.type":1},{sparse:true})

Note: If you query documents by info.type it will skip documents which do not have the field as it will use the index. Use .explain() to see what it is doing.

##Index Utilization The index will be used by the query if the requested field are in the right order. Therefore if the index is based on {tags, name} and the query is {tags, name} or {tags} then the index will be used. However, if the query is {name} then the index will not be used. This is because it evaluates the use of the index from left to right, so if we are looking for name then when it finds the first element of the index is tags it discards it and checks then next index.

##Covering Index A covering index is where the index contains all the information required. So if an index is create on name and all we want to know is if the name is in the database then by omitting the _id from the returned values we can use a covering index.

db.animals.find({name:'cat'},{_id:0,name:1}).explain()

{
	"cursor" : "BtreeCursor name_1",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 0,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 0,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : true,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {
		"name" : [
			[
				"cat",
				"cat"
			]
		]
	},
	"server" : "NDX0001:27017",
	"filterSet" : false
}

Note that "indexOnly" : true, this indicates that it did not need to search outside the index. If we included the _id field in the returned values it would need to retrieve the document to get the _id and therefore it would be "indexOnly" : false.