In previous post, we discussed how indexes work in Cosmos DB. In this post, we are going to look at the various types of indexes available in Cosmos DB.
Azure Cosmos DB supports three types of indexes:
1. Range Index: Range Indexes are based on an ordered tree like structure. An ordered tree is a data structure where nodes split into multiple branches. If every node has two branches, then it is called a binary tree. Range indexes are used for the following:
- Equality queries ( WHERE property IN (“value1”, “value2”, “value3”))
- Range queries ( WHERE property > ‘value’) (also works with
<,>=,<=,!=) - String system functions (WHERE CONTAINS(property, “value”)
- ORDER BY queries
- JOIN queries
2. Spatial Index: This type of index is used to improve the query performance for geospatial data.
There are three keywords that can be used with Spatial indexes:
ST_DISTANCE:
WHERE ST_DISTANCE(c.property, { "type": "Point", "coordinates": [0.0, 10.0] }) < 40ST_WITHIN:
WHERE ST_WITHIN(c.property, {"type": "Point", "coordinates": [0.0, 10.0] } })ST_INTERSECTS:
WHERE ST_INTERSECTS(c.property, { 'type':'Polygon', 'coordinates': [[ [31.8, -5], [32, -5], [31.8, -5] ]] })3. Composite Indexes: For increasing the performance of queries that use multiple fields, composite indexes are used.
Below are some ways to use composite indexes:
ORDER BY on multiple properties:
ORDER BY c.property1, c.property2Filter and ORDER BY:
WHERE c.property1 = 'value' ORDER BY c.property1, c.property2Filter on two or more properties where at least one property is an equality filter:
WHERE c.property1 = 'value' AND c.property2 > 'value'Reference: https://docs.microsoft.com/en-us/azure/cosmos-db/index-overview
Azure Data Engineering 