In Cassandra how to select rows by multiple filters?

Question

I have a table like below: CREATE TABLE test ( a int; b int; c int; d int; PRIMARY KEY () );

I want to select the data with 0 < a < 10 and 0 < b < 10, how should I set the PRIMARY KEY and how should I run the CQL query?

Thanks!

G Quintana G Quintana · Accepted Answer · 2014-10-29T10:14:22

Using range operators (< >) on the partitioning key is not allowed unless you use the ByteOrderedPartitioner which has several drawbacks related to performance.

Else, with the default Murmur3Partitioner, you have two options. First solution:

create table test (
    a int,
    b int,
    c int,
    d int,
    primary key ((a,b))
);

Then for X in 1..10

select * from test where a = X and b in (1,2,3,4,5,6,7,8,9,10)

Second solution:

create table test (
    a int,
    b int,
    c int,
    d int,
    primary key (a,b)
);

Then

select * from test where a in (1,2,3,4,5,6,7,8,9,10) and b >=1 and b <= 10 ;

In Cassandra how to select rows by multiple filters?

1 Answers