What is the best way to retrieve all values from a Cassandra database table when all partition keys are unique?

For my application I have a table as following:

create table companies(id uuid, name text, ...., primary key((id)));

Now for my admin panel and for background jobs I need to be able to retrieve all of the companies and loop through all the rows in my code. What would be the best approach for this?

I know I can just perform a select query without a partition (primary) key but this is bad because it will contact all nodes since every row is stored on random nodes in the datacenter.

select * from companies;

Now one thing I could do is create a dummy key which will always be the same for every row and all the rows will be stored in the same partition, but this is also very bad since the table will grow and could reach more than 1k rows.

create table companies(fake_key text, id uuid, name text, ... primary key((fake_key), id));
insert into companies(fake_key, id, name) values ('app', uuid(), 'company_a');
insert into companies(fake_key, id, name) values ('app', uuid(), 'company_b');

Should I create a table in for example a MySQL database and create a new row in the MySQL database every time I create a new companies row in Cassandra?

Unfortunately, there isn't going to be a one-size-fits-all solution since you have a special use case.

If performance matters then store it in a single partition with clustered rows. As you already pointed out, it can be problematic since this solution won't scale if the partition grows unbounded. However, 1-2K rows isn't going to be so bad since you're only really storing the company names.

Storing the data in another relational DB isn't going to be much of a benefit since it'll just increase the level of complexity in your app plus the fact that you need to contend with the challenges of a managing another infrastructure. Cheers!