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 https://stackoverflow.com/questions/43183915/distinct-count-in-cassandra or here https://stackoverflow.com/questions/1951843/row-count-of-a-column-family-in-cassandra or here https://stackoverflow.com/questions/35640503/count-rows-in-table — Horia, Mar 19 '18 at 15:18

score 2 · Answer 1 · answered Mar 19 '18 at 15:50

2

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.

answered Mar 19 '18 at 15:50

Alex Ott

80,552
8
87
132

please i don't understand when u said "you need to make sure that it's not updated from 2 place?". Concurrent updates on the same column (unread int static) can't guarantee consistency? – Christian Ibanibo Mar 21 '18 at 11:53
Please i still don't understand your answer to this question. Am confused, i need further clarification. How will i get the total unread messages? each unread field (int static) belongs to each record. – Christian Ibanibo Mar 28 '18 at 13:39
If you want to make sure that data isn't overwritten by another instance, then you need to put conditional update, like, `UPDATE table SET unread = 1 IF unread = 2` – Alex Ott Mar 28 '18 at 19:58

Cassandra unread messages count

1 Answers1