36722

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

Answer1:

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;

Recommend

  • array_push() expects parameter 1 to be array, null given php error message
  • sending message in telegram bot using curl
  • get blank page result when using send image function telegram api on codeigniter
  • Specifying field size of Map collection in grails DOM
  • Spark DataFrame equivalent to Pandas Dataframe `.iloc()` method?
  • What is #:: method
  • Escaping a LIKE pattern or regexp string in Postgres 8.4 inside a stored procedure
  • TelephonyManager crashing on android studio
  • How do I get an Option instead of an Option from a Diesel query which only returns 1 or 0 records?
  • ASP Error 0223 - TypeLib Not Found, intermittent, resolved after IIS restart
  • Socket io in node app on google app engine
  • Play WS (2.2.1): post/put large request
  • Why querying a date BC is changed to AD in Java?
  • Set the selected item in dropdownlist in MVC3
  • Database structure design with variable amounts of fields
  • Python CGI os.system causing malformed header
  • NHibernate Validation Localization with S#arp Architecture
  • DomPDF {PAGE_NUM} not on first page
  • Why value captured by reference in lambda is broken? [duplicate]
  • How can I send an e-mail from a vbs script
  • Illegal mix of collations for operation for date/time comparison
  • Accessing IRQ description array within a module and displaying action names
  • AES padding and writing the ciphertext to a disk file
  • How to convert from System.Drawing.Color to Excel.ColorFormat in C#? Change comment color
  • Why doesn't :active or :focus work on text links in webkit? (safari & chrome)
  • Validaiting emails with Net.Mail MailAddress
  • MySQL WHERE-condition in procedure ignored
  • Web-crawler for facebook in python
  • Delete MySQLi record without showing the id in the URL
  • Unanticipated behavior
  • Comma separated Values
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • trying to dynamically update Highchart column chart but series undefined
  • Trying to get generic when generic is not available
  • Why joiner is not used after Sequence generator or Update statergy
  • Getting Messege Twice Using IMvxMessenger
  • Recursive/Hierarchical Query Using Postgres
  • UserPrincipal.Current returns apppool on IIS
  • java string with new operator and a literal
  • How can I use threading to 'tick' a timer to be accessed by other threads?