2

Using Cassandra as db:

Say we have this schema primary_key((id1),id2,type) with index on type, because we want to query by id1 and id2.

Does query like SELECT * FROM my_table WHERE id1=xxx AND type='some type' going to perform well?

I wonder if we have to create and manage another table for this situation?

Improve this question

1 Answer 1

Reset to default
1

The way you are planning to use secondary index is ideal (which is rare). Here is why:

  • you specify the partition key (id1) in your query. This ensures that only the relevant partition (node) will be queried, instead of hitting all the nodes in the cluster (which is not scalable)
  • You are (presumably) indexing an attribute of low cardinality (I can imagine you have maybe a few hundred types?), which is the sweet spot when using secondary indexes.

Overall, your data model should perform well and scale. Yet, if you look for optimal performances, I would suggest you use an additional table ((id1), type, id2).

Finale note: if you have a limited number of type, you might consider using solely ((id1), type, id2) as a single table. When querying by id1-id2, just issue a few parallel queries against the possible value of type.

The final decision needs to take into account your target latency, the disk usage (duplicating table with a different primary key is sometimes too expensive), and the frequency of each of your queries.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you, pretty solid suggestion. Your final note looks very ideal but unfortunately, this microservice is agnostic of what types it stores. Great suggestion tho.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.