Dynamodb global/local secondary index, searching via category and rating

Question

I have a table of songs in Dynamodb that looks like this:

I wish to return to my app a list of songs by two conditions "Category" and "UserRating"

At present my hash key is "Artist" and rangekey is "Songtitle".

I think that if I made a secondary key "Category" I could search for all the songs in a particular category and similarly I could do this for rating but I don't know how to do this for both?

I also believe I understand the understand the difference between the global and local index.

So what I am thinking (which is probably not correct) is that I need to create a global secondary index on "Category" and do a query on the attribute "UserRating".

Will this work? And even if this works is this the correct way to be doing it?

Thanks

Answer 1

With query you can only search for the Hash (now the partition key) and optionally the range (now the sort key). This has to drive your table and index design.

In your case if wish to query Category on its own then you'd create a new GSI with Category as the partition key. If you want to search within a Category for songs with a rating of something, then you'd create that index with a partition key of Category and a sort key of Rating.

If you need to query by rating alone, then you'd have to create a GSI with rating as the partition key. Bear in mind however you can't do anything like "greater than" or "between" on the partition key: you can only do this on the sort key.

One other factor to consider is expected performance. Amazon advise that partition keys have high cardinality. It is called the partition key because it is the means by which the data is physically organised into partitions. If you have an index with x number of rows across only a few categories, then your data will not be well distributed, which causes a potential performance bottleneck. For non-serious projects this won't be noticeable however.

Hope this helps somewhat.