Indexing Strategies - ( CosmosDB Indexing and Querying )

Introduction

When designing a database, indexing is an important consideration to optimize query performance. CosmosDB is a NoSQL database that supports flexible schema design and multiple indexing strategies. In this tutorial, we will explore different indexing strategies that can be used with CosmosDB, and how to query the data using these indexes.

Syntax

Example index policy

{
  "indexingMode": "consistent",
  "includedPaths": [
    {
      "path": "/*",
      "indexes": [
        {
          "kind": "Range",
          "dataType": "Number",
          "precision": -1
        },
        {
          "kind": "Hash",
          "dataType": "String",
          "precision": 3
        }
      ]
    }
  ]
}

Explanation

CosmosDB provides two types of indexes - Range and Hash. Range indexes are used for range queries on numerical and string data types. Hash indexes are used for Equality queries on all data types.

The indexing policy for a container can be defined at the time of creation or can be updated later. The policy defines the included and excluded paths for indexing, along with the type of index to be created for each path.

Example

Suppose we have a collection of documents containing employee information. The document structure is as follows:

{
    "id": "<guid>",
    "firstName": "John",
    "lastName": "Doe",
    "department": "Sales",
    "salary": 50000,
    "employeeId": "10001"
}

We can create an index on the salary field using the following policy:

{
  "includedPaths": [
    {
      "path": "/salary/?",
      "indexes": [
        {
          "kind": "Range",
          "dataType": "Number",
          "precision": -1
        }
      ]
    }
  ]
}

This would create a Range index on the salary field, allowing us to efficiently query by salary range.

Output

Suppose we want to query all employees with a salary above 60000. We can use the following query:

SELECT * FROM c WHERE c.salary > 60000

With the range index we defined on the salary field, CosmosDB can quickly perform the query and return the results.

Use

The choice of indexing strategy depends on the specific use case and querying patterns of your application. Range indexing is useful for queries that involve range comparisons on numerical or string data types. Hash indexing is useful for equality queries on all data types.

For complex queries, a composite index can be created by combining multiple individual indexes. CosmosDB also provides spatial and full-text search indexing for specialized use cases.

Important Points

• Indexing is an important consideration to optimize query performance in CosmosDB.
• CosmosDB supports multiple indexing strategies, including Range and Hash indexes.
• The indexing policy can be defined at the time of container creation or updated later.
• Range indexing is useful for range comparisons on numerical or string data types.
• Hash indexing is useful for equality queries on all data types.
• Composite indexes can be created by combining multiple individual indexes.
• CosmosDB also provides spatial and full-text search indexing for specialized use cases.

Summary

In this tutorial, we explored different indexing strategies that can be used with CosmosDB to optimize query performance. We looked at how to create and use Range and Hash indexes and discussed composite indexing for complex queries. We also highlighted specialized indexing options for spatial and full-text search.