Is this a non-CQL trick? If not, then would it be possible for you to give me the CQL3 CREATE TABLE definition instead?
I'm not entirely sure what you mean by "UserId, UserId, UserScore:Score" and then FriendId, Score, etc. On Thu, Jan 23, 2014 at 5:06 PM, Colin Clark <co...@clark.ws> wrote: > One of tricks I've used a lot with cassandra is a sparse df definition and > inserted columns programmatically that weren't in the definition. > > I'd be tempted to look at putting a users friend list on one row, the row > would look like this: > > ROWID COLUMNS > > UserID UserId, UserID, UserScore:Score FriendID, score > FriendID, score .... > > The "UserID" and "UserScore" columns are literal, the FriendID's are > either literal or keys into the user cf. > > When a user gets a new score, you update that user's row and a general > update query updating all rows with that userid with the new score > > That way, all friends are on the same row, which makes query easy. And > you can still issue query to find the top score across the entire userbase > by querying userid, and userscore. > > Is this a better explanation of my previous and lame explanation? > > 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 >>> >> >> >
