Dynamo Composed Local Secondary Indexes

Question

I'm trying to set up a new DynamoDB table and I'm planning to use the Local Secondary Indexes (LSI) for the first time.

However, I'm finding myself struggling creating them. I don't find any reference in the Amazon API in creating LSI's with composed hash keys (Hash key that exists out of more than one attribute).

This is how my model looks like, it represent a message send from UserA to UserB:

• UserAId
• UserBId
• dateTime
• status (1, 2, 3, 4, 5)
• isPriority (0 or 1)
• isNewsLetter (0 or 1)

The queries that I would like to do are the following

1. Get All messages from UserAId ordered by date
2. Get All messages from UserAId to UserBId
3. Get All messages from UserAId with a certain status (1 or 2) ordered by dateTime
4. Get All messages from UserAId with a certain status (1 or 2) For today ordered by dateTime
5. Get All priority messages from UserAId ordered by dateTime
6. Get All messages from UserAId with a certain status (1 or 2) and where isNewsLetter = 1 ordered by status

For the hashKey I have chosen UserAId, Range key is dateTIme. This should cover the query that I need to execute the most (query 1) and should spread out the data evenly.

For the other queries, I was thinking about the following LSI's

• Query 2: Hash UserAId, Range UserBId
• Query 3: Hash UserAId, Range (composed) status-dateTime. This way I can query with BEGINS_WITH 1 AND BEGINS_WITH 2, Order by date should be auto then I think
• Query 4: Same as above
• Query 5: Hash UserAId, Range (composed) isPriority-dateTime. Query BEGINS_WITH 1, order should go auto then I think
• Query 6: Hash UserAId, Range (composed) status-isNewsLetter. This way it should order according to status I think

My questions are:

• Is it possible to use composed keys in LSI? How do I define them? there is nothing about this in the docs provided by Amazon. This is how I define a normal LSI:

'KeySchema' => array(
  array('AttributeName' => 'UserAId', 'KeyType' => 'HASH'),
  array('AttributeName' => 'status',  'KeyType' => 'RANGE'),
),

So how do I define there to use status-isNesLetter for example?

• Will my assumptions of the ordering be true?

Thanks in advance

Answer 1

After doing some research and some testing, I can now answer my own question.

Question 1) No, you cannot define composed range keys in the keyschema. You can however create seperate attributes in your model class to represent the same thing

Example: For Query 3 you would need to add a new attribute to the model and do the merging of the attributes status and dateTime yourself.

Question 2) Yep. You can also use ScanIndexForward to change the order to DESC/ASC