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) );

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;