1

I came to know that Cassandra doesn't support OR in where clause, but I need an equivalent for the following query : 

select * from table where column1 = '123' or column2 = '123';

As OR is not supported in Cassandra can I use Batch queries like the following and combine the result in the program and is it efficient?

[ select * from table where column1 = '123' , select * from table where column2 = '123']

If not is there any way to achieve the above stated simple logic.

In my case, both column1 and column2 together are the Primary key. i.e. composite partition key.

Can I combine column1 and column2 as a list [column1, column2] and query like :

select * from table where list contains '123';

and creating secondary index on the list

naveen
  • 560
  • 6
  • 15
  • Also `BATCH` in Cassandra does't do what you think it does. It applies operations _atomically_. There is nothing to be gained by applying `SELECT`s atomically. – Aaron May 28 '20 at 12:29

1 Answers1

1

As you said you can't query with or, also you can't execute these two queries without using secondary index or allow filtering.

select * from table where column1 = '123' 
select * from table where column2 = '123'

according to these queries, possibilities are;

  • column1 or column2 is partition key
  • column1 and column2 are partition key
  • one of them is partition key and other is clustering key.

and you can't query them according to these possibilities. If you want to query them without allow filtering, then you need to create secondary index. After that you can make two queries and compare them on your application layer. Secondary index is not a silver bullet, there are drawbacks which are well explained in here and here.

If you need those type of or queries then it may be better to redesign your tables according to the queries to avoid secondary indexes like these or allow filtering.

Ersoy
  • 8,816
  • 6
  • 34
  • 48