amazon web services - DynamoDB still reads all items when using KeyConditionExpression BETWEEN - Stack Overflow

admin管理员组

文章数量:1324856

My DynamoDB table schema is:

• identifier (HASH KEY)
• time (RANGE KEY)

I'm trying to improve the response time in a DynamoDB PartiQL query that uses a KeyConditionExpression and BETWEEN.

My 1st query is:

select * from "table_test" where identifier = "product_1" and time > "-1" limit=100

Because of limit=100, DynamoDB automatically paginates results with LastEvaluatedKey:

[-1, 50000]
[50000, 100000]  
[100000, 150000]
[150000, 200000] (focus on this partition, it takes 4 seconds)
[200000, null]

My 2nd query is:

select * from "table_test" where identifier = "product_1" and time between 150000 and 200000

Query #2 takes 10 seconds.

I don't know why query #2 still takes more time than query #1 for the partition 150000-200000.

My question is does DynamoDB still read all items before applying the "BETWEEN" condition?

My DynamoDB table schema is:

• identifier (HASH KEY)
• time (RANGE KEY)

I'm trying to improve the response time in a DynamoDB PartiQL query that uses a KeyConditionExpression and BETWEEN.

My 1st query is:

select * from "table_test" where identifier = "product_1" and time > "-1" limit=100

Because of limit=100, DynamoDB automatically paginates results with LastEvaluatedKey:

[-1, 50000]
[50000, 100000]  
[100000, 150000]
[150000, 200000] (focus on this partition, it takes 4 seconds)
[200000, null]

My 2nd query is:

select * from "table_test" where identifier = "product_1" and time between 150000 and 200000

Query #2 takes 10 seconds.

I don't know why query #2 still takes more time than query #1 for the partition 150000-200000.

My question is does DynamoDB still read all items before applying the "BETWEEN" condition?

• amazon-web-services
• amazon-dynamodb
• partiql

Share Improve this question Follow asked Jan 15 at 1:00 Tam Ho ChiTam Ho Chi 111 silver badge11 bronze badge 1

• Tips for asking a good Structured Query Language (SQL) question – DarkBee Commented Jan 15 at 6:03

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

PartiQL defaults to a Scan when 2 or more conditions are on the where clause. I suggest you use the Query API which will give you the performance you desire.

本文标签： amazon web servicesDynamoDB still reads all items when using KeyConditionExpression BETWEENStack Overflow