CQL check if record exists

Question

I'm on my path to learning Cassandra, and the differences in CQL and SQL, but I'm noticing the absence of a way to check to see if a record exists with Cassandra. Currently, the best way that I have is to use

SELECT primary_keys FROM TABLE WHERE primary_keys = blah,

and checking to see if the results set is empty. Is there a better way to do this, or do I have the right idea for now?

score 21 · Answer 1 · answered Aug 25 '16 at 14:49

21

Using count will make it traverse all the matching rows just to be able to count them. But you only need to check one, so just limit and return whatever. Then interpret the presence of the result as true and absence - as false. E.g.,

SELECT primary_keys FROM TABLE WHERE primary_keys = blah LIMIT 1

answered Aug 25 '16 at 14:49

Nikita Volkov

42,792
11
94
169

7

If you include all primary key columns in the query, then it's not possible for there to be more than one result? – OrangeDog Aug 26 '16 at 14:38
This is the most effective solution – Michal May 21 '19 at 14:09

score 9 · Accepted Answer · answered Dec 26 '15 at 21:32

That's the usual way in Cassandra to check if a row exists. You might not want to return all the primary keys if all you care about is if the row exists or not, so you could do this:

SELECT count(*) FROM TABLE WHERE primary_keys = blah,

This would just return a 1 if the row exists, and a 0 if it doesn't exist.

score 1 · Answer 3 · edited May 23 '17 at 10:32

If you are using primary key to filter rows, all the above 3 solutions (including yours) are fine. And I don't think there are real differences.

But if you are using a general way (such as indexed column, partition key) to filter rows, you should take the solution of "Limit 1", which will avoid useless network traffic.

There is a related example at: The best way to check existence of filtered rows in Cassandra? by user-defined aggregate?

CQL check if record exists

3 Answers3