Implement a composite index

In this article, we will explore how to design and implement native applications using Microsoft Azure Cosmos DB by focusing on the implementation of a composite index. A composite index is a powerful feature that allows you to optimize queries by combining multiple properties into a single index. By using composite indexes effectively, you can improve query performance and reduce the amount of data that needs to be scanned.

Enabling Composite Index

To get started, let’s assume that we have a collection in our Cosmos DB database that stores information about exams. Each document in the collection represents an exam and contains properties such as examId, studentId, courseId, and score. Our goal is to create a composite index that can efficiently query exams based on a combination of studentId and courseId.

First, we need to enable the composite index feature for our collection. To do this, follow the steps below:

1. Navigate to the Azure portal.
2. Open the Cosmos DB account that contains your database.
3. Go to the “Data Explorer” and select your database and collection.
4. Click on the “Scale & Settings” tab and then click on the “Composite Index” tab.
5. Enable the “Composite Indexing” feature if it’s not already enabled.

Creating a Composite Index

Once the feature is enabled, we can create a composite index by defining the paths for the properties that we want to include in the index. In our case, we want to include the studentId and courseId properties. To create the index, we need to specify these paths in the correct order. The order of the paths is important because it determines the order of the index keys.

Here’s an example code snippet that demonstrates how to create a composite index using the Azure Cosmos DB SDK:

using Microsoft.Azure.Cosmos;
using System.Threading.Tasks;

await container.IndexingPolicy.CreateIndexingPolicyAsync(new Cosmos.IndexingPolicy
{
CompositeIndexes = new Collection<>>()
{
new Collection()
{
new Cosmos.CompositePath()
{
Path = "/studentId",
Order = Cosmos.SortOrder.Ascending
},
new Cosmos.CompositePath()
{
Path = "/courseId",
Order = Cosmos.SortOrder.Ascending
}
}
}
});


Make sure to replace your_cosmosdb_endpoint_uri and your_cosmosdb_primary_key with the appropriate values for your Cosmos DB account. Also, provide the desired IDs for your database and container.

In the code snippet above, we create an instance of CosmosClient by providing the endpoint URI and primary key. Then, we create or retrieve the database and container using their IDs. Finally, we define the composite index by specifying the paths for the studentId and courseId properties and their sort order. In this example, we set the sort order to be ascending for both.

After executing the code, the composite index will be created for the specified paths. Cosmos DB will automatically maintain and update the index as new documents are inserted, updated, or deleted.

Querying Using the Composite Index

Now, we can efficiently query the exams collection using the composite index. For example, to retrieve all exams for a particular student and course, execute the following code snippet:

using Microsoft.Azure.Cosmos;
using System.Linq;
using System.Threading.Tasks;

while (iterator.HasMoreResults)
{
FeedResponse response = await iterator.ReadNextAsync();
foreach (Exam exam in response)
{
// Process each exam
}
}


Again, ensure that you provide the correct values for your_cosmosdb_endpoint_uri, your_cosmosdb_primary_key, your_database_id, and your_container_id. Also, replace your_student_id and your_course_id with the desired values for your query.

In the code snippet, we create a LINQ query that filters exams based on the studentId and courseId properties. The composite index we created earlier will be utilized to efficiently retrieve the desired results. We then iterate through the results using a feed iterator.

By implementing a composite index in Microsoft Azure Cosmos DB, we can optimize queries involving multiple properties and improve the performance of our native applications. This allows us to efficiently retrieve the data we need while minimizing the amount of data scanned by the database.