Sample Header Ad - 728x90

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

0 votes
1 answer
117 views
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: 1) **What do you suggest I can do, so that (game_id, user_id) is unique, yet (game_id, score) is indexed?** 2) 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?
Asked by Nuno (829 rep)
Jul 8, 2023, 08:05 PM
Last activity: Jul 18, 2023, 08:08 AM