Cassandra unread messages count

Question

Please am trying to model unread messages count. I have a user_messages table

    CREATE TABLE user_messages (
      user text,
      sender text,
      message text,
      read boolean,
      creation_date timestamp,
      PRIMARY KEY (user, creation_date)
    );

How can i know the total number of unread messages? Do i need to create a counter table and update the user_messages table every time a message is read?
I also read that using select count(*) is too expensive and not recommended for production. Please I need a help.

You could use the solutions described here stackoverflow.com/questions/43183915/… or here stackoverflow.com/questions/1951843/… or here stackoverflow.com/questions/35640503/count-rows-in-table — Horia

Alex Ott Alex Ott · Accepted Answer · 2018-03-19T15:50:29

You may use static column to keep the number of unread messages, and update it when you read message (but you need to make sure that it's not updated from 2 places). Something like:

CREATE TABLE user_messages (
  user text,
  unread int static,
  sender text,
  message text,
  read boolean,
  creation_date timestamp,
  PRIMARY KEY (user, creation_date)
);

and then fetching the count of unread messages will be simple query (or narrow to specific fields):

select * from user_messages limit 1;

and get the unread field from the answer.

P.S. Regarding the count(*) - it's costly when you don't specify the partition key, but when you count inside the partition (in your case if you have when user = ?) - then it's less expensive, but still will have significant overhead because of transferring data. And because the read flag isn't a clustering column, you can't put limit on it.

Cassandra unread messages count

1 Answers