Range Queries in Cassandra (CQL 3.0)

Question

One main part of Cassandra that I don't fully understand is its range queries. I know that Cassandra emphasizes distributed environment and focuses on performance, but probably because of that, it currently only support several types of ranges queries that it can finish efficiently, and what I would like to know is that: which types of range queries are supported by Cassandra.

As far as I know, Cassandra supports the following range queries:

1: Range Queries on Primary key with keyword TOKEN, for example:

 CREATE TABLE only_int (int_key int PRIMARY KEY);
 ...
 select * from only_int where token(int_key) > 500;

2: Range Queries with one equality condition on a secondary index with keyword ALLOW FILTERING, for example:

CREATE TABLE example (
  int_key int PRIMARY KEY,
  int_non_key int,
  str_2nd_idx ascii
);
CREATE INDEX so_example_str_2nd_idx ON example (str_2nd_idx);
...
select * from example where str_2nd_idx = 'hello' and int_non_key < 5 allow filtering;

But I am wondering if I miss something and looking for a canonical answer which lists all types of range queries supported by the current CQL (or some work-around that allows more types of range queries).

Answer 1

You can look for clustering keys. A primary key can be formed by a partitioning key and then by clustering keys.

for example definition like this one

CREATE TABLE example (
  int_key int,
  int_non_key int,
  str_2nd_idx ascii,
  PRIMARY KEY((int_key), str_2nd_idx)
);

will allow to you make queries like these without using token

select * from example where str_2nd_idx < 'hello' allow filtering;

Before creating a TABLE in cassandra you should start from thinking about queries and what you want to ask from the data model in cassandra.

Answer 2

Apart from the queries you mentioned, you can also have queries on "Composite Key" column families (well you need to design your DB using composite keys, if that fits your constrains). For an example/discussion on this take a look at Query using composite keys, other than Row Key in Cassandra. When using Composite Keys you can perform other types of queries, namely "range" queries that do not use the "partition key" (first element of the composite key) - normally you need to set the "allow filtering" parameter to allow these queries, and also can perform "order by" operations on those elements, which can be very interesting in many situations. I do think that composite key column families allow to overcome several (necessary) "limitations" (to grant performance) of the cassandra data model when compared with the "extremely flexible" (but slow) model of RDBMS...

Answer 3

1) Create table:

create table test3(name text,id int,address text,num int,primary    key(name,id,address))with compact storage;

2) Inserting data into table:

insert into test3(name,id,address,num) values('prasad',1,'bangalore',1)  ;

insert into test3(name,id,address,num) values('prasad',2,'bangalore',2)  ;

insert into test3(name,id,address,num) values('prasad',3,'bangalore',3)  ;

insert into test3(name,id,address,num) values('prasad',4,'bangalore',4)  ;

3)

select * from test3  where name='prasad' and id <3;

4)

name   | id | address   | num


--------+----+-----------+-----

prasad |  1 | bangalore |   1

prasad |  2 | bangalore |   2