[RESOLVED] Cannot execute this query as it might involve data filtering

While querying on Apache Cassandra Database, it can be seen the following error message thrown,

Caused by: com.datastax.driver.core.exceptions.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
.....

Cause of the issue

If the above error is thrown, it means that Cassandra assumes that it my not be able to execute the query is efficient manner.

The possible option is to fetch all the data and filter out the data with non-matching value for the column.

So Cassandra is warning you about this behavior and passing you the selection of choice correctly to solve the issue.

Suggesting to “ALLOW FILTERING” does not mean it has to be done blindly. It is always better to revisit the data model.

So the options are,

• Remodeling the data and revise the DDLs
• Allow Filtering

Basically it can be like to change or update the primary key to support querying.

Example

CREATE TABLE user (userId int, 
                    username text, 
                    password text, 
                    role text, 
                    PRIMARY KEY(userId));

If you are querying by username on the above user table, while you are setting userId as primary key, Cassandra will throw the above error on this scenario as it needs a full table scan on querying.

• NOT RECOMMENDED : assume that the table will contain small number of entries and allow filtering in the query. 

        select * from XXX  WHERE XXX ALLOW FILTERING;

• RECOMMENDED - Update the table creation query to have username in primary key if it is allowed by the requirment. 

CREATE TABLE user (userId int, 
                    username text, 
                    password text, 
                    role text, 
                    PRIMARY KEY(username, userId));

References

https://www.datastax.com/dev/blog/allow-filtering-explained-2