How the IN query works internally and when to use it

ScyllaDB
1

I have one question regarding “IN CLAUSE.” How does it work internally?
Example: My user table has the following PRIMARY KEY: PRIMARY KEY((tenant_id, user_id))
My Requirement is Get multiple users from the users table using tenant_id and user_id.

  1. I can use IN QUERY, e.g.:
    select * from user where tenant_id=‘testtenant’ and user_id in (‘64d73074-1bd1-486e-b717-265b1d46c022’,‘64d73074-1bd1-486e-b717-265b1d46c022’);

  2. I can get individual users by using the partition key. (+Redis caching of frequent users)I want to understand how “in query” works internally. If you have any posts, that would be useful.
    What approach is better for fetching such data if:

    • Users count is below 100
    • More than 100

*Originally asked on the User Slack channel.

2

The coordinator breaks up the query and sends individual ones for each partition key. It’s generally better to send separate queries if they have different partition keys but use IN if everything is in the same partition.
So it’s more of a convenience - the coordinator handles sending queries concurrently for you.