MongoDB – Compound Indexes

MongoDB provides indexing for efficient execution of queries without indexes MongoDB has to search every document to match the query which is highly inefficient. Indexes are easy to traverse and store sorted documents according to the specified fields. Unlike single field index in which indexing is done on a single field, Compound Indexes does indexing on multiple fields of the document either in ascending or descending order i.e. it will sort the data of one field, and then inside that it will sort the data of another field. Or in other words, compound indexes are those indexes where a single index field contains references to multiple fields. In MongoDB, the compound index can contain a single hashed index field, if a field contains more than one hashed index field then MongoDB will give an error.

How to create a compound Index?

In MongoDB, we can create compound index using createIndex() method.

Syntax:

db.collection.createIndex({<field1>: <type1>, <field2>: <type2>, …})

Here <type> represents the value of the field in the index specification describes the kind of index for that field. For example, a value 1 for indexing in ascending order or value -1 for indexing in descending order. 

 For example:

{"id":"1", "product":"chips", "manufacturer":"lays", "price":20} 
{"id":"2", "product":"pringles", "manufacturer":"Kellogg's", "price":99}
{"id":"3", "product":"Doritos", "manufacturer":"lays", "price":10}
{"id":"4", "product":"cheetos", "manufacturer":"lays", "price":473}
{"id":"5", "product":"coldrink", "manufacturer":"mountain-dew", "price":20}

In the above four documents, we can index them on both the field’s name and age in any order. Suppose we create a compound index by db.collection.createIndex(manufacturer:1, price:-1) than index will look somewhat like this

As shown in the picture firstly Kellogs, Lays and mountain-dew are sorted alphabetically, and then the prices are sorted inside them. Here the indexing will be firstly done in ascending order of the manufacturer name and after that indexing will be done in descending order on price. So we can run queries like db.collection.find().sort(manufacturer:1, price: -1) efficiently as we have created an index for that.

Examples –

In the following examples, we are working with:

Database: GeeksforGeeks

Collection: products

Documents: Six documents that contain the details of the employees in the form of field-value pairs.

Creating a compound index on manufacture and price:

Here we are creating an index on manufacture in ascending order and then on price in descending order.

db.products.createIndex({manufacturer:1, price:-1})

Creating a compound index on product, manufacturer, and price:

Here we are creating an index on the product in ascending order then it will be sorted for the manufacturer in ascending order, and then it will again be sorted for price

db.products.createIndex({product:1,manufacturer:1,price:1}) 

Sorting using Compound Indexes 

We can use the sort() function of MongoDB on the created index as indexes contain ordered records, MongoDB can obtain the results of a sort from an index with which are Sort expression matches(matching using prefix). If MongoDB cannot use an index to obtain the sort order it performs a blocking sort operation on the data in which it consumes and processes all input documents to the sort before returning results.

If the sort keys match an index prefix, MongoDB can use the index to sort the query results. A prefix is a subset that consists of one or more keys of the index key pattern.

For e.g. suppose we create a compound index by 

db.data.createIndex({a:1, b: -1, c:1})

Then we have the following prefixes on which our created index is used for sorting –

{a:1}
{a:1,b:-1}
{a:1,b-1,c:1}

Example	prefix
db.data.find().sort({a: 1})	{a: 1}
db.data.find().sort({a: -1})	{a: 1}
db.data.find().sort({a: 1, b: -1})	{a: 1, b: -1}
db.data.find().sort({a: -1, b: 1})	{a: 1, b: -1}
db.data.find().sort({a: 1, b: -1, c: 1})	{a: 1, b: -1, c: 1}

So for all the examples in the above table MongoDB will use our created index but not on db.data.find().sort({a: -1, b: -1}) or db.data.find().sort({a: 1, b: 1}) as they don’t match any prefix for these two MongoDB has to perform blocking sort.

We can also use non-prefix like {b: -1, c: 1} for sorting but for this, we have to put equality on the preceding prefix key .for e.g.

db.data.find({a: 6}).sort({b: -1, c: 1})

Here we have put an equality condition on key ‘a’ and now it will use prefix {a: 1, b: -1, c: 1} 

Example:

db.products.find().sort({manufacturer:1,price:-1}) 

In the above example since our sort key pattern has manufacture:1, price:-1 which is similar to the second index manufacture_1_price_-1 key thus this index will be used to obtain result instead of doing sort again.