Hi Ben,
if need very limited number of positions (as you said ten) may be you can store them in LIST of UDT? Or just as JSON string? So you'll have one row per each pair user-video. It can be something like this: CREATE TYPE play (position int, last_time timestamp); CREATE TABLE recent (user_name text, video_id text, review LIST<frozen<play>>, PRIMARY KEY (user_name, video_id)); UPDATE recent set review = review + [(1234,12345)] where user_name='some user' AND video_id='great video'; UPDATE recent set review = review + [(1234,123456)] where user_name='some user' AND video_id='great video'; UPDATE recent set review = review + [(1234,1234567)] where user_name='some user' AND video_id='great video'; You can delete the oldest entry by index: DELETE review[0] FROM recent WHERE user_name='some user' AND video_id='great video'; or by value, if you know the oldest entry: UPDATE recent SET review = review - [(1234,12345)] WHERE user_name='some user' AND video_id='great video'; Best regards, Vladimir Yudovin, Winguzone - Hosted Cloud Cassandra Launch your cluster in minutes. ---- On Mon, 07 Nov 2016 21:54:08 -0500ben ben <diamond....@outlook.com> wrote ---- Hi guys, We are maintaining a system for an on-line video service. ALL users' viewing records of every movie are stored in C*. So she/he can continue to enjoy the movie from the last point next time. The table is designed as below: CREATE TABLE recent ( user_name text, vedio_id text, position int, last_time timestamp, PRIMARY KEY (user_name, vedio_id) ) It worked well before. However, the records increase every day and the last ten items may be adequate for the business. The current model use vedio_id as cluster key to keep a row for a movie, but as you know, the business prefer to order by the last_time desc. If we use last_time as cluster key, there will be many records for a singe movie and the recent one is actually desired. So how to model that? Do you have any suggestions? Thanks! BRs, BEN
