Cassandra asks for ALLOW FILTERING even though column is clustering key

Question

Very new to Cassandra so apologies if the question is simple.

I created a table:

create table ApiLog (
LogId uuid,     
DateCreated timestamp,
ClientIpAddress varchar,
primary key (LogId, DateCreated));

This work fine:

select * from apilog

If I try to add a where clause with the DateCreated like this:

select * from apilog where datecreated <= '2016-07-14'

I get this:

Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING

From other questions here on SO and from the tutorials on datastax it is my understanding that since the datecreated column is a clustering key it can be used to filter data.

I also tried to create an index but I get the same message back. And I tried to remove the DateCreated from the primary key and have it only as an index and I still get the same back:

create index ApiLog_DateCreated on dotnetdemo.apilog (datecreated);

Answer 1

The partition key LogId determines on which node each partition will be stored. So if you don't specify the partition key, then Cassandra has to filter all the partitions of this table on all the nodes to find matching data. That's why you have to say ALLOW FILTERING, since that operation is very inefficient and is discouraged.

If you specify a specific LogId, then Cassandra can find the partition on a single node and efficiently do a range query by the clustering key.

So you need to plan your schema such that you can do your range queries within a single partition and not have to do a full table scan like you're trying to do.