Collocating Data when having more than one Reference Key

Question

I would like to understand how to collocate data in Ignite if I have more than one reference key.

Provided an example MySQL table structure below.

CREATE TABLE users(
    user_id INTEGER,
    user_name VARCHAR(250),
    age INTEGER,
    CONSTRAINT pk_user PRIMARY KEY (user_id)
);

CREATE TABLE movies(
    movie_id INTEGER,
    movie_name VARCHAR(250),
    genre VARCHAR(250),
    CONSTRAINT pk_movie PRIMARY KEY (movie_id)
);

CREATE TABLE ratings(   
    user_id INTEGER,
    movie_id INTEGER,
    rating FLOAT,
    timestamp TIMESTAMP,
    CONSTRAINT pk_rating PRIMARY KEY (user_id, movie_id)
);

Here I have "ratings" table which is having reference to "user_id" from users table and "movie_id" from movies table.

How to collocate the data correctly in this scenario.

Should I try to collocate users and movies where the "ratings" data exists? Is it even possible?

Answer 1

You can't really do both. The best practice would be to optimise for the common scenario. For example, if 80% of your queries join movies and ratings, you collocate them. Queries involving users will still work, they just hit the network.

Depending on the number of movies (or users) you could also consider replicating that cache rather than partitioning.