CODEWITHSUNDEEP
cassandracassandra-2.0
Ced
Ced

Reputation: 17337

ranking where the score is a function of time

I would like to migrate my db which is currently on mysql to C*. At the moment I have a table that I have trouble imagining how to "migrate" it.

Entity

Where hotscore is f(s,d) = log10 + (s.t/45000). S is score and t is timestamp since epoch.

Essentially what I would be looking into querying is the top 20 of that entity. With mysql and a cron job I'm updating the hotscore every minute. For that reason hot score cannot be suited for a partition key. I'm trying to see if I can make this happen before moving to c*. As far as I know a primary key like (id, hotscore) wouldn't be good because it means C* has to scan every entry.

Upvotes: 0

Views: 76

Answers (1)

Jim Meyer
Jim Meyer

Reputation: 9475

You'll soon be able to handle this use case with materialized views when Cassandra 3.0 is released.

See an example of ordering rows in a materialized view here and here.

The way it works is in your base table you don't use the score as a clustering column, but you do use it as a clustering column in the materialized view. Then when you update the base table, the ordering in the view is automatically updated.

Upvotes: 1

Related Questions