How can I make (game_id, user_id) unique, yet (game_id, score) indexed/clustered, in ScyllaDB?

Question

See this in ScyllaDB:

CREATE TABLE scores_leaderboards (
    game_id int,
    score int,
    user_id bigint,
    PRIMARY KEY (game_id, score, user_id)
) WITH CLUSTERING ORDER BY (score DESC);

The idea is that we can get the user IDs with the top scores for a game.

This means that (game_id, score) needs to be indexed, and that's why I put it like that in the Primary Key.

However, I had to include user_id, so that 2 users can have the exact same score.

The problem is that, like this, (game_id, user_id) isn't unique. I want to make sure the table never contains 2+ pairs of the same (game_id, user_id).

My questions:

What do you suggest I can do, so that (game_id, user_id) is unique, yet (game_id, score) is indexed?
Ideally, (game_id, user_id) would be the primary key, and then I'd create a compound index with (game_id, score). However, if I try to create a compound index,

CREATE INDEX scores_leaderboards_idx ON scores_leaderboards (game_id, score);

I get the following:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Only CUSTOM indexes support multiple columns"

But I'm not finding how I can create a CUSTOM index... is this an extension I need to install?
Is there any recommendation against using custom indexes?

Erick Ramirez · Answer 1 · 2023-07-17T06:56:54.240

You're making it more convoluted than it needs to be. It would be so much simpler just to partition the table by game ID and clustered by the scores.

Here is what I recommend:

CREATE TABLE top_scores_by_game_id (
    game_id int,
    score int
    user_id int,
    PRIMARY KEY (game_id, score)
) WITH CLUSTERING ORDER BY (score DESC)

To get the top 10 high scores for a game:

SELECT score, user_id FROM top_scores_by_game_id
  WHERE game_id = ?
  LIMIT 10

If your app needs to list the top scores for a user, you should design a table specifically for this app query. Something like:

CREATE TABLE top_scores_by_user (
    user_id int,
    game_id int,
    score int,
PRIMARY KEY (user_id, game_id)

)

To only store the highest score for each game, you should include a conditional statement (using lightweight transactions). Let me illustrate with an example. In this table, a user has a high score of 100 for game ID 1:

 user_id | game_id | score
---------+---------+-------
       1 |       1 |   100

This UPDATE statement will not get applied because the score is lower than what already exists in the table:

cqlsh> UPDATE top_scores_by_user SET score = 95 WHERE user-id = 1 AND game_id = 1 IF score < 95;
[applied] | score
-----------+-------
     False |   100

However, if we attempt to update the record with a higher score of 195 then the conditional update will succeed:

cqlsh> UPDATE top_scores_by_user SET score = 195 WHERE user-id = 1 AND game_id = 1 IF score < 195;
[applied]
  True

How can I make (game_id, user_id) unique, yet (game_id, score) indexed/clustered, in ScyllaDB?

1 Answers1

[applied]