Cassandra Allow filtering

Question

I have a table as below

CREATE TABLE test (
 day int,
 id varchar,  
 start int,
 action varchar,  
 PRIMARY KEY((day),start,id)
);

I want to run this query

Select * from test where day=1 and start > 1475485412 and start < 1485785654 
and action='accept' ALLOW FILTERING

Is this ALLOW FILTERING efficient?

I am expecting that cassandra will filter in this order

1. By Partitioning column(day)
2. By the range column(start) on the 1's result
3. By action column on 2's result. 

So the allow filtering will not be a bad choice on this query.

In case of the multiple filtering parameters on the where clause and the non indexed column is the last one, how will the filter work? Please explain.

Answer 1

Is this ALLOW FILTERING efficient?

When you write "this" you mean in the context of your query and your model, however the efficiency of an ALLOW FILTERING query depends mostly on the data it has to filter. Unless you show some real data this is a hard to answer question.

I am expecting that cassandra will filter in this order...

Yeah, this is what will happen. However, the inclusion of an ALLOW FILTERING clause in the query usually means a poor table design, that is you're not following some guidelines on Cassandra modeling (specifically the "one query <--> one table").

As a solution, I could hint you to include the action field in the clustering key just before the start field, modifying your table definition:

CREATE TABLE test (
 day int,
 id varchar,  
 start int,
 action varchar,  
 PRIMARY KEY((day),action,start,id)
);

You then would rewrite your query without any ALLOW FILTERING clause:

SELECT * FROM test WHERE day=1 AND action='accept' AND start > 1475485412 AND start < 1485785654

having only the minor issue that if one record "switches" action values you cannot perform an update on the single action field (because it's now part of the clustering key), so you need to perform a delete with the old action value and an insert it with the correct new value. But if you have Cassandra 3.0+ all this can be done with the help of the new Materialized View implementation. Have a look at the documentation for further information.