Most of the work I've done like this has used sparse table definitions and the empty column trick. I didn't explain that very well in my last response.
I think by using the userid as the rowid, and using the friend id as the column name with the score, that I would put an entire user's friend list on one row. The row would look like this: ROWID USERID Colin +1 320 221 9531 On Thu, Jan 23, 2014 at 2:34 AM, Kasper Middelboe Petersen < kas...@sybogames.com> wrote: > What would the consequence be of having this updated highscore table > (using friendId as part of the clustering index to avoid name collisions): > > CREATE TABLE highscore ( > userId uuid, > score int, > friendId uuid, > name varchar, > PRIMARY KEY(userId, score, friendId) > ) WITH CLUSTERING ORDER BY (score DESC); > > And then create an index: > > CREATE INDEX friendId_idx ON highscore ( friendId ); > > The table will have many million (I should expect 100+ million) entries. > Each friendId would appear as many times as the user has friends. It sounds > like a scenario where I should take care of using a custom index. > > I haven't worked with custom indexes in Cassandra before, but I assume > this would allow me to query the table based on (userId, friendId) for > updating highscores. > > But what would happen in this case? What queries would be affected and > roughly to what degree? > > Would this be a viable option? > > > > On Wed, Jan 22, 2014 at 6:44 PM, Kasper Middelboe Petersen < > kas...@sybogames.com> wrote: > >> Hi! >> >> I'm a little worried about the data model I have come up with for >> handling highscores. >> >> I have a lot of users. Each user has a number of friends. I need a >> highscore list pr friend list. >> >> I would like to have it optimized for reading the highscores as opposed >> to setting a new highscore as the use case would suggest I would need to >> read the list a lot more than I would need write new highscores. >> >> Currently I have the following tables: >> CREATE TABLE user (userId uuid, name varchar, highscore int, bestcombo >> int, PRIMARY KEY(userId)) >> CREATE TABLE highscore (userId uuid, score int, name varchar, PRIMARY >> KEY(userId, score, name)) WITH CLUSTERING ORDER BY (score DESC); >> ... and a tables for friends - for the purpose of this mail assume >> everyone is friends with everyone else >> >> Reading the highscore list for a given user is easy. SELECT * FROM >> highscores WHERE userId = <id>. >> >> Problem is setting a new highscore. >> 1. I need to read-before-write to get the old score >> 2. I'm screwed if something goes wrong and the old score gets overwritten >> before all the friends highscore lists gets updated - and it is an highly >> visible error due to the same user is on the highscore multiple times. >> >> I would very much appreciate some feedback and/or alternatives to how to >> solve this with Cassandra. >> >> >> Thanks, >> Kasper >> > >
