DynamoDB: searching with primary key or range key, which way is faster

dynamodbnosqlprimary-key

To those familiar with DynamoDB, I'm trying to make a table where items only have 2 entries. For example, . Most of the time I'll be searching the user_id with a facebook_id on hand.

Amazon's DynamoDB only provides the ability to search based on 2 keys, the primary key and the range key. I have 2 options; either making the user_id or the facebook_id the primary key. Primary and Range keys differ on the matching algorithm used.

I'm just curious which way is more efficient in this case? Searching with the primary key, or searching with the range key, or is there really no difference?

Could we add DynamoDB as a tag? As DynamoDB is fairly new, I'll not be surprised if no-one knows the answer.

Best Answer

Are you trying to determine if to query only using the hash OR only the range key? Unless I'm misunderstanding your question, you are not able to to perform a query with just the range keys. For example, if your range key was the date, you cannot query with just two dates, you MUST also specify the hash key. Remember when using both hash and range keys, the primary key IS both the hash and range, however the range can be optional.

With that said, I suggest specifying the facebook_id as the hash and your user_id as an attribute not the range. Use this if only your logic is using the facebook_id as a lookup id to your application specific user_id.

Related Solutions

Sql-server – Single Identity column and composite key, which to make primary

This seriously depends on the expected half-life of your project, on how agile your environment is and so on. If your project is there to last, and changes are possible, I would not assume that your composite PK is never going to change. I have seen too many projects burned by such assumptions.

One good example would be the use of Social Security Number. Long ago it was a common practice to use it as a natural PK. Later on, many systems had to restrict its use for privacy reasons, and it became not possible to propagate SS#s all over child tables.

The systems that used identities adjusted to the change easily. The systems that used SS#s in child tables had to go through a major overhaul.

So, in many cases it is cheaper to use identities as PKs, adding a UNIQUE constraint on what currently is considered to be unique, and referring child tables to ParentID. This way we have to do less work to adjust when the situation changes.

Sql-server – When do you add your foreign keys

I typically create the primary key constraint directly with the table:

CREATE TABLE dbo.MyTable
(MyTableID INT IDENTITY(1,1) NOT NULL
     CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED,
 .......
)

I don't see any point in deferring that PK constraint to later (same also applies to DEFAULT and CHECK constraints which I typically also define "inline" with the table creation script).

However, the FK constraint, I typically define those as separate ALTER TABLE .. ADD CONSTRAINT ... statements, that can be run right after the table creation, or after all tables have been created - doesn't really matter:

ALTER TABLE dbo.MyTable WITH CHECK
ADD CONSTRAINT FK_MyTable_OtherTable
FOREIGN KEY (OtherTableID) REFERENCES dbo.OtherTable(OtherTableID)

ALTER TABLE dbo.MyTable CHECK CONSTRAINT FK_MyTable_OtherTable

Best Answer

Related Solutions

Sql-server – Single Identity column and composite key, which to make primary

Sql-server – When do you add your foreign keys

Related Question