英文:
How do you use a very long composite index in Cosmos DB?
问题
I'm here to provide the translated content for you. Here is the translated text:
"我正试图降低我的GET操作的RU,因为我有大量的记录,但99%的时间只想返回最近的记录。
因此,我添加了一个复合索引,其中包括我查询的所有字段。
这些字段也都作为普通的范围索引包含在内。然而,即使给它足够的时间来完成更新,我的查询仍然以完全相同的RU成本和速度运行。
以下是我的查询样例,我只是不确定如何指定我要使用这个复合索引。
这个是否不是使用复合索引的正确时机?试图使这个尽可能快速。"
英文:
I am trying to get the RU of my GET operations down, as I have loads of records but 99 percent of the time only want to return the most recent record.
So I added a Composite Index, this includes all of the fields that I'm querying on.
[
{
"path": "/resourceIdentity/subscription",
"order": "ascending"
},
{
"path": "/resourceIdentity/resourceGroup",
"order": "ascending"
},
{
"path": "/resourceIdentity/extensionProviderNamespace",
"order": "ascending"
},
{
"path": "/resourceIdentity/extensionResourceType",
"order": "ascending"
},
{
"path": "/resourceIdentity/extensionResourceName",
"order": "ascending"
},
{
"path": "/resourceIdentity/resourceType",
"order": "ascending"
},
{
"path": "/resource/systemData/createdAt",
"order": "descending"
}
]
These fields are all included as normal range indexes too. However even after giving it time to finish updating, my queries still run with literally the exact same RU cost and speed.
Here's what my query looks like, I'm just not sure how to specify that I want to use this composite index.
SELECT top 1 * FROM root WHERE ((((((root["resourceIdentity"]["subscription"] = "de453a71-7d81-493d-bce6-224fec7223a9")
AND (LOWER(root["resourceIdentity"]["resourceGroup"]) = "newyears_group"))
AND (LOWER(root["resourceIdentity"]["extensionProviderNamespace"]) = "x.compute"))
AND (LOWER(root["resourceIdentity"]["extensionResourceType"]) = "virtualmachines"))
AND (LOWER(root["resourceIdentity"]["extensionResourceName"]) = "newyearsro1"))
AND (LOWER(root["resourceIdentity"]["resourceType"]) = "xResource/reports"))
order by root["resource"]["systemData"]["createdAt"] desc
Is this just not the right time to use a composite index? Trying to make this as fast as possible
答案1
得分: 1
根据文档中的说明,
> 如果查询在一个或多个属性上进行过滤,并且在ORDER BY子句中具有不同的属性,将这些属性添加到ORDER BY子句中可能会有所帮助。
因此,为了利用复合索引,您需要按照在复合索引中定义的顺序将它们全部包含在您的ORDER BY子句中。因此,您可以尝试保持复合索引不变,但更改您的查询为:
SELECT top 1 * FROM root WHERE ((((((root["resourceIdentity"]["subscription"] = "de453a71-7d81-493d-bce6-224fec7223a9")
AND (LOWER(root["resourceIdentity"]["resourceGroup"]) = "newyears_group"))
AND (LOWER(root["resourceIdentity"]["extensionProviderNamespace"]) = "x.compute"))
AND (LOWER(root["resourceIdentity"]["extensionResourceType"]) = "virtualmachines"))
AND (LOWER(root["resourceIdentity"]["extensionResourceName"]) = "newyearsro1"))
AND (LOWER(root["resourceIdentity"]["resourceType"]) = "xResource/reports"))
ORDER BY root["resourceIdentity"]["subscription"] ASC, root["resourceIdentity"]["resourceGroup"] ASC, root["resourceIdentity"]["extensionProviderNamespace"] ASC , root["resourceIdentity"]["extensionResourceType"] ASC, root["resourceIdentity"]["extensionResourceName"] ASC, root["resourceIdentity"]["resourceType"] ASC, root["resource"]["systemData"]["createdAt"] DESC
这应该不会影响您的查询结果,但应该会更有效地使用您的复合索引。
英文:
From the documentation it states,
> If a query filters on one or more properties and has different
> properties in the ORDER BY clause, it may be helpful to add the
> properties in the filter to the ORDER BY clause.
So in order to utilize the composite indexes, you need to include all of them in your ORDER BY clause in the same order as you defined in your composite index.
So can you try keeping the composite indexes as is but change your query to :
SELECT top 1 * FROM root WHERE ((((((root["resourceIdentity"]["subscription"] = "de453a71-7d81-493d-bce6-224fec7223a9")
AND (LOWER(root["resourceIdentity"]["resourceGroup"]) = "newyears_group"))
AND (LOWER(root["resourceIdentity"]["extensionProviderNamespace"]) = "x.compute"))
AND (LOWER(root["resourceIdentity"]["extensionResourceType"]) = "virtualmachines"))
AND (LOWER(root["resourceIdentity"]["extensionResourceName"]) = "newyearsro1"))
AND (LOWER(root["resourceIdentity"]["resourceType"]) = "xResource/reports"))
order by root["resourceIdentity"]["subscription"] asc, root["resourceIdentity"]["resourceGroup"] asc, root["resourceIdentity"]["extensionProviderNamespace"] asc , root["resourceIdentity"]["extensionResourceType"] asc, root["resourceIdentity"]["extensionResourceName"] asc, root["resourceIdentity"]["resourceType"] asc, root["resource"]["systemData"]["createdAt"] desc
This should have no impact in your query results but it should start using your composite indexes more effectively.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论