I never thought about using a synthetic key, but in this instance with about a dozen rows it's probably ok. Thanks for your great idea.

Where  did you read about the synthetic key idea? I've not come across it before.


Jabbar Azam

On 4 May 2013 19:30, Dave Brosius <dbrosius@mebigfatguy.com> wrote:
if you want to store all the roles in one row, you can do

create table roles (synthetic_key int, name text, primary key(synthetic_key, name)) with compact storage

when inserting roles, just use the same key

insert into roles (synthetic_key, name) values (0, 'Programmer');
insert into roles (synthetic_key, name) values (0, 'Tester');

and use

select * from roles where synthetic_key = 0;

(or some arbitrary key value you decide to use)

the that data is stored on one node (and its replicas)

of course if the number of roles grows to be large, you lose most of the value in having a cluster.

On 05/04/2013 12:09 PM, Jabbar Azam wrote:

I want to create a simple table holding user roles e.g.

create table roles (
       name text,
       primary key(name)

If I want to get a list of roles for some admin tool I can use the following CQL3

select * from roles;

When a new name is added it will be stored on a different host and doing a select * is going to be inefficient because the table will be stored across the cluster and each node will respond. The number of roles may be less than or just greater than a dozen. I'm not sure if I'm storing the roles correctly.

The other thing I'm thinking about is that when I've read the roles once then I can cache them.


Jabbar Azam