composite index

Question

composite index

Yang Chow Mun 121

I am working on composite index for cosmos db and have a few doubts on it.

Let said my composite index is


            {
                "path": "/id",
                "order": "ascending"
            },
            {
                "path": "/year",
                "order": "ascending"
            },
            {
                "path": "/date",
                "order": "descending"
            }

a. if my query is " Select * from database c where c.id='a' and c.year=2023 and c.month=8 and c.date=9", does the composite index help?

b. if my query is " Select * from database c where c.id='a' and c.year=2023 and c.date=8 and c.time=1", does the composite index help?

c. if my query is " Select * from database c where c.year=2023 and c.id='a' and c.date=8, does the composite index help?

d. if my query is " Select * from database c where c.id='a' and c.date=8 and c.year>2022 and c.year<2025, does the composite index help?

Appreciate your advice! thanks

1 answer

Your answer

Answer 1

Oury Ba-MSFT 20,716 Microsoft Employee

@Yang Chow Mun Thank you for reaching out.

Yes, the composite index will help with query a, c and d. But for b, the composite index might not help because the query has a property time which is not included in the composite index. The composite index only includes id, year and date.

Please learn more with the below links.

Composite indexed policy examples

Composite index

New ways to use composite indexes in Azure Cosmos DB

Hope that helps.

Regards,

Oury

Oury Ba-MSFT 20,716 Reputation points Microsoft Employee

2023-08-09T18:03:04.0633333+00:00

Please don't forget to mark as accept answer rif the reply was helpful.

Regards,

Oury
Yang Chow Mun 121 Reputation points

2023-08-10T00:47:31.5333333+00:00
Hi Qury, thanks for your reply. I have viewed the links you shared but the explanation in the document doesn't cover such details.

In view of your reply, does it mean

the order in the composite index is not important.

it does not require the full match of fields in the composite index for it to work?

thanks!
Yang Chow Mun 121 Reputation points

2023-08-10T00:51:51.4533333+00:00

duplicate ==
Oury Ba-MSFT 20,716 Reputation points Microsoft Employee

2023-08-17T00:02:28.1766667+00:00

Yang Chow Mun

Sorry, let me clarify this about composite index.

To perform a query that has an ORDER BY clause for multiple properties, a composite index is required on those properties. If the query includes filters along with sorting on multiple properties, you may need more than one composite index.

Queries that have an ORDER BY clause with two or more properties require a composite index.

You won't need composite index on the above queries.

Read more from our documentation Overview of indexing in Azure Cosmos DB

By default, indexing policy is set to automatic. It's achieved by setting the automatic property in the indexing policy to true. Setting this property to true allows Azure Cosmos DB to automatically index items as they're written

Regards,

Oury

Share via

composite index

1 answer

Your answer