cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Donald Smith <Donald.Sm...@audiencescience.com>
Subject RE: Data Modelling Help
Date Wed, 29 Apr 2015 16:01:01 GMT
Secondary indicies are inefficient and are deprecated, as far as I know.

Unless you store many thousands of emails for a long time (which I recommend against), just
use a single table with the partition key being the userid and the timestamp being the clustering
(column) key, as in your schema.   You might want to use a TTL to expire old emails.

If you need to store a huge number of emails, consider splitting into tables by year, for
example.

If you had two tables (one for read emails and one for unread emails) you’d have to move
rows between them when an email got marked (un)read.  But it would support efficiently finding
(un)read emails.

Don

From: Sandeep Gupta [mailto:sandy.pec@gmail.com]
Sent: Monday, April 27, 2015 11:46 AM
To: user@cassandra.apache.org
Subject: Fwd: Data Modelling Help

Hi,

I am a newbie with Cassandra and thus need data modelling help as I haven't found a resource
that tackles the same problem.

The user case is similar to an email-system. I want to store a timeline of all emails a user
has received and then fetch them back with three different ways:

1. All emails ever received
2. Mails that have been read by a user
3. Mails that are still unread by a user

My current model is as under:

CREATE TABLE TIMELINE (
    userID varchar,
    emailID varchar,
    timestamp bigint,
    read boolean,
    PRIMARY KEY (userID, timestamp)
) WITH CLUSTERING ORDER BY (timestamp desc);

CREATE INDEX ON TIMELINE (userID, read);

The queries I need to support are:

SELECT * FROM TIMELINE where userID = 12;
SELECT * FROM TIMELINE where userID = 12 order by timestamp asc;
SELECT * FROM TIMELINE where userID = 12 and read = true;
SELECT * FROM TIMELINE where userID = 12 and read = false;
SELECT * FROM TIMELINE where userID = 12 and read = true order by timestamp asc;
SELECT * FROM TIMELINE where userID = 12 and read = false order by timestamp asc;


Queries are:

1. Should I keep  read as my secondary index as It will be frequently updated and can create
tombstones - per http://docs.datastax.com/en/cql/3.1/cql/ddl/ddl_when_use_index_c.html its
a problem.

2. Can we do inequality check on secondary index because i found out that atleast one equality
condition should be present on secondary index

3. If this is not the right way to model, please suggest on how to support the above queries.
Maintaining three different tables worries me about the number of insertions (for read/unread)
as number of users * emails viewed per day will be huge.


Thanks in advance.

Best Regards!





Keep Walking,
~ Sandeep



Mime
View raw message