I am new to Cassandra from 20 years of RDBMS, and over the last few days have been reading and watching everything I can get my hands on that applies to my situation. I'm sure this is a basic question, but for some reason it's not clicking so please excuse me if this is answered elsewhere.
I am trying to design a table to store a list of associations between users. Any user may have any number of associated users. It's sort of like a friends list.
Right now I have 3 fields:
id (timeuuid) (PK)
I want to be able to do:
SELECT * FROM friends WHERE user = ?
So because user is not a PK, it cannot be used in a query. And, if I make it part of the PK, it has to be unique, meaning users can only have 1 friend max.
I sort of solved this by using a secondary index on user, but I imagine that approach is not the best idea. The query is rather slow to respond, obviously because it has to ask all my cluster nodes for their part of the data.
So: what is the proper way to design this table? Thank you very much in advance for any guidance.