1

I have a dilemma and I know I should of used an SQL DB from the beginning.

I am unsure if I can use a sort key for my particular use case. I have a table that contains multiple attributes brand, model ref, reference... What I am trying to do is let the user select brand then the model then the reference etc then get all products that match that criteria and give a mean of the prices of those items.

Now doing a scan operation of the whole DB that has 300K+ items is not very cost effect to say the least but this is the situation I am in.

My question is how can I most cost effectively do what I want to do?

Joe Hill
  • 11
  • 5

1 Answers1

0

Let the table T have only a partition key: ID.

For the sake of the simplicity you let your client choose n = 3 attributes: brand, model-ref, reference.

Now, define a Global Secondary Index (GSI) with partition key: brand_model-ref_reference and sorting key: ID. I suggest you to use Projection: ALL.

Thus, when your client has chosen its 3 values: a, b, c, all you have to do is to query the GSI with brand_model-ref_reference = "a#b#c". You will efficiently fetch all and only the items you need to compute your average. The size of the table is no longer of any importance.

Notes:

  • With this solution you have to fix in advance the number of criteria and the client must choose a value for all of them. Not so nice.
  • If there are more constraints all that solution becomes useless. Use it as a hint. :)
Costin
  • 2,699
  • 5
  • 25
  • 43