MongoDB - Compound Indexes

MongoDB Compound Indexes are an advanced feature designed to enhance query performance by indexing multiple fields within a collection. These indexes allow MongoDB to quickly locate documents based on multiple criteria, significantly improving efficiency and speed.

In this article, we will explore how compound indexes work, their benefits, and how to create and use them along with practical examples.

MongoDB Compound Indexes

MongoDB Compound Indexes is an index on multiple fields in a MongoDB collection. It can be created to enhance the performance of queries that need to filter on multiple fields. A MongoDB compound index can cover queries that request only the indexed fields, improving efficiency by avoiding reading the actual documents.

Compound Indexes do 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. In other words, compound indexes are those indexes where a single index field contains references to multiple fields.

Create a Compound Index

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

Syntax:

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

Here <type> represents the value of the field in the index specification and 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 of Compound Indexes

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.

Example 1: 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})

Output:

Example 2: 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

Query:

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

Output:

Sorting using Compound Indexes 

• We can use the sort() function for sorting the data into particular Sort Order 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}

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}) 

Output:

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. 

Conclusion

MongoDB compound indexes are a robust mechanism for enhancing the performance of queries that involve multiple fields. By creating these indexes, we can significantly improve the performance and efficiency of your database operations. Understanding how to create and use compound indexes effectively is essential for maximizing the potential of your MongoDB collections.