Hello Ben

 Try the following alternative with composite partition key to encode the dual states of the boolean:

create table x(
  id text,
  flag boolean,
  timestamp timeuuid,
    // other fields
  primary key ((id,flag) timestamp)
)

Your previous "select * from x where flag = true;"  translate into:

 SELECT * FROM x WHERE id=... AND flag = true

Of course, you'll need to provide the id in any case.

 If you want to query only on the boolean flag, I'm afraid that manual indexing or secondary index (beware of cardinality !) are your only choices.

Regards

 Duy Hai DOAN




On Fri, Mar 21, 2014 at 8:27 PM, Ben Hood <0x6e6562@gmail.com> wrote:
Hi,

I was wondering what the best way is to lay column families out so
that you can to query by a boolean attribute.

For example:

create table x(
  id text,
  timestamp timeuuid,
  flag boolean,
  // other fields
  primary key (id, timestamp)
)

So that you can query

select * from x where flag = true;

Three approaches spring to mind:

1) Put a secondary index on the flag column
2) Split the column family definition out into two separate CFs, one
for true and one for false
3) Maintain a manual index table
4) Something else

Option (1) seems to be the easiest, but I was wondering if that was
going to put too much load on the secondary index, given the low
cardinality of this attribute. It seems like what Cassandra would have
to do internally to achieve (1) is to effectively implement (2) or (3)
behind the scenes, but I'm just guessing.

Does anybody have any experience with this?

Cheers,

Ben