Filter on the partition and the clustering key with an additional criteria

Question

I want to filter on a table that has a partition and a clustering key with another criteria on a regular column. I got the following warning.

InvalidQueryException: 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

I understand the problem if the partition and the clustering key are not used. In my case, is it a relevant error or can I ignore it?

Here is an example of the table and query.

CREATE TABLE mytable(
    name text,
    id uuid,
    deleted boolean
    PRIMARY KEY((name),id)
)

SELECT id FROM mytable WHERE name='myname' AND id='myid' AND deleted=false;

Ashraful Islam · Accepted Answer · 2017-07-20T10:49:50.723

In Cassandra you can't filter data with non-primary key column unless you create index in it.

Cassandra 3.0 or up it is allowed to filter data with non primary key but in unpredictable performance

Cassandra 3.0 or up, If you provide all the primary key (as your given query) then you can use the query with ALLOW FILTERING, ignoring the warning

Otherwise filter from the client side or remove the field deleted and create another table :

Instead of updating the field to deleted true move your data to another table let's say mytable_deleted

CREATE TABLE mytable_deleted (
    name text,
    id uuid
    PRIMARY KEY (name, id)
);

Now if you only have the non deleted data on mytable and deleted data on mytable_deleted table

or

Create index on it :

The column deleted is a low cardinality column. So remember

A query on an indexed column in a large cluster typically requires collating responses from multiple data partitions. The query response slows down as more machines are added to the cluster. You can avoid a performance hit when looking for a row in a large partition by narrowing the search.

Filter on the partition and the clustering key with an additional criteria

1 Answers1