The best composite indexes for my query.

green 76 Reputation points
2021-04-09T08:36:53.21+00:00

I have a query below. 

SELECT *
FROM c
WHERE
  c.partition = "partition" AND
  c.date BETWEEN "2020-01-01T00:00:00.000Z" AND "2020-12-31T23:59:59.999Z" AND
  ARRAY_CONTAINS(["group_a", "group_b"], c.groups) AND
  CONTAINS(c.name, "name", true) AND
  c.deleted = null
ORDER BY
  c.sequence DESC

I'd like to add composite indexes for this query.
What index is best for it?

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,439 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Navtej Singh Saini 4,216 Reputation points Microsoft Employee
    2021-04-13T01:22:03.103+00:00

    @green

    Please go through the considerations for the range and order by part of the query. Specifically if you are using the multiple range queries or system functions, it should be the last part of the index and only one of these used in single index.

    87114-image.png

    Please check and get back to us with questions.

    Regards
    Navtej S