MongoDB Advanced Indexing

MongoDB Advanced Indexing

As shown below, we inserted the following document into the collection of named users.

db.users.insert(
	{
		"address": {
			"city": "Delhi",
			"state": "Uttar pradesh",
			"pincode": "099123"
		},
		"tags": [
			"music",
			"cricket",
			"blogs"
		],
		"name": "Raj"
	}
)

The above document contains an address sub-document and a tags array.

Indexing Array Fields

Suppose we want user documents to be checked based on user tags. We'll create an index for the tag array in the collection for this purpose.

In turn, creating an array index creates separate index entries for each of your fields. So in our case, when we create an index on the tag array, for its music, cricket and blog values, separate indexes will be created.

Use the following code to build an index on the tag array –

>db.users.createIndex({"tags":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
>

After creating the index, we can search on the tags field of the collection like this −

> db.users.find({tags:"cricket"}).pretty()
{
	"_id" : ObjectId("5dd7c927f1dd4583e7103fdf"),
	"address" : {
		"city" : "Los Angeles",
		"state" : "California",
		"pincode" : "099123"
	},
	"tags" : [
		"music",
		"cricket",
		"blogs"
	],
	"name" : "Raj"
}
>

To verify that proper indexing is used, use the following explain command −

>db.users.find({tags:"cricket"}).explain()

This gives you the following result −

{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "mydb.users",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"tags" : {
				"$eq" : "cricket"
			}
		},
		"queryHash" : "9D3B61A7",
		"planCacheKey" : "04C9997B",
		"winningPlan" : {
			"stage" : "FETCH",
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"tags" : 1
				},
				"indexName" : "tags_1",
				"isMultiKey" : false,
				"multiKeyPaths" : {
					"tags" : [ ]
				},
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 2,
				"direction" : "forward",
				"indexBounds" : {
					"tags" : [
						"[\"cricket\", \"cricket\"]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "Krishna",
		"port" : 27017,
		"version" : "4.2.1",
		"gitVersion" : "edf6d45851c0b9ee15548f0f847df141764a317e"
	},
	"ok" : 1
}
>

The above command resulted in "cursor" : "BtreeCursor tags_1" which confirms that proper indexing is used.

Indexing Sub-Document Fields

Suppose we want to search for documents based on fields like city, state, and pincode. Since all these fields are part of the sub-document address field, we will produce an index for all the sub-document fields.

To build an index on all three sub-document fields, use the following code –

>db.users.createIndex({"address.city":1,"address.state":1,"address.pincode":1})
{
	"numIndexesBefore" : 4,
	"numIndexesAfter" : 4,
	"note" : "all indexes already exist",
	"ok" : 1
}
>

Once the index is created, we can search as follows for any of the sub-document fields using this index.

> db.users.find({"address.city":"Los Angeles"}).pretty()
{
	"_id" : ObjectId("5dd7c927f1dd4583e7103fdf"),
	"address" : {
		"city" : "Delhi",
		"state" : "uttar pradesh",
		"pincode" : "099123"
	},
	"tags" : [
		"music",
		"cricket",
		"blogs"
	],
	"name" : "Raj"
}  

Note that the expression of the query must follow the order of the specified index. So the above index will support the following queries –

>db.users.find({"address.city":"Los Angeles","address.state":"California"}).pretty()
{
	"_id" : ObjectId("5dd7c927f1dd4583e7103fdf"),
	"address" : {
		"city" : "Delhi",
		"state" : "Uttar pradesh",
		"pincode" : "123"
	},
	"tags" : [
		"music",
		"cricket",
		"blogs"
	],
	"name" : "Raj"
}
>