Distinct count in Cassandra

Question

I have a table chat_matches in "cassandra". And want to get the count of all messages from_id to distinct to_id with count of rows(group by to_id).

CREATE TABLE chat_matches(
    chat_id uuid,
    from_id bigint,
    to_id bigint,
    message  text,
    message_send_time timestamp,
    PRIMARY KEY ((chat_id,from_id),message_send_time)
);

You want from_id,to_id,count(*) group by from_id,to_id query? — nevsv

Ashraful Islam Ashraful Islam · Accepted Answer · 2017-04-03T13:13:39

In cassandra count(*) is a very costly operation, need to scan all the row from all the node just to give you the count and can generate timeout exception.

So Instead of using count(*) maintain a counter table like the below one :

CREATE TABLE message_counter (
    from_id bigint,
    to_id bigint,
    count counter,
    primary key((from_id, to_id ))
);

When a new message appears just increment the value of count by one.

 UPDATE message_counter SET count = count + 1 WHERE from_id = 1 AND to_id = 2;

Now you can select the message count group by from_id to to_id very efficiently

SELECT * FROM message_counter WHERE from_id = 1 AND to_id = 2;

Distinct count in Cassandra

1 Answers