Cassandra query by primary key and a column throws error

Question

Schema

CREATE TABLE books (
   isbn text PRIMARY KEY,
   author text
);

insert into books (isbn, author) values ('111', 'Sally');
insert into books (isbn, author) values ('112', 'Fred');
insert into books (isbn, author) values ('113', 'Joe');

With the above data, I am able to query via primary key 111

select * from books where isbn = '111';

However, when I put author in the where condition it throws error

select * from books where isbn = '111' and author = 'Fred';

Query 1 ERROR: 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 am not able to understand that if the data is already filtered by the primary key (which is only one record) why does it throw an error ?

Second, if I use allow filtering is there any performance impact?

Edit: https://dzone.com/articles/apache-cassandra-and-allow-filtering has given me some clue.

Steven Hollingsworth Steven Hollingsworth · Accepted Answer · 2019-04-27T04:14:43

This link might help you. From what I understand there is a performance hit with cassandra if you allow filtering (guessing it's similar to SQL RBAR, which is slow, and why it throws and error about filtering)

I'm still relatively new to cassandra, but from what I've read you need to run the select query to include ALL columns defined in your primary key, IF you haven't defined a secondary index. But there are limitations with secondary indexes.

HTH Good luck.

Cassandra query by primary key and a column throws error

2 Answers