Primary Key related CQL3 Queries cases & errors when sorting

Question

I have two issues while querying Cassandra:

Query 1

> select * from a where author='Amresh' order by tweet_id DESC;
Order by with 2ndary indexes is not supported

What I learned: secondary indexes are made to be used only with a WHERE clause and not ORDER BY? If so, then how can I sort?

Query 2

> select * from a where user_id='xamry' ORDER BY tweet_device DESC;
Order by currently only supports the ordering of columns following their
declared order in the PRIMARY KEY.

What I learned: The ORDER BY column should be in the 2nd place in the primary key, maybe? If so, then what if I need to sort by multiple columns?

Table:

CREATE TABLE a(
  user_id      varchar,
  tweet_id     varchar,
  tweet_device varchar,
  author       varchar,
  body         varchar,
  PRIMARY KEY(user_id,tweet_id,tweet_device)
);

INSERT INTO a (user_id, tweet_id, tweet_device, author, body)
    VALUES ('xamry', 't1', 'web', 'Amresh', 'Here is my first tweet');
INSERT INTO a (user_id, tweet_id, tweet_device, author, body)
    VALUES ('xamry', 't2', 'sms', 'Saurabh', 'Howz life Xamry');
INSERT INTO a (user_id, tweet_id, tweet_device, author, body)
    VALUES ('mevivs', 't1', 'iPad', 'Kuldeep', 'You der?');
INSERT INTO a (user_id, tweet_id, tweet_device, author, body)
    VALUES ('mevivs', 't2', 'mobile', 'Vivek', 'Yep, I suppose');

Create index user_index on a(author);

William Price William Price · Accepted Answer · 2014-09-11T08:59:08

To answer your questions, let's focus on your choice of primary key for this table:

PRIMARY KEY(user_id,tweet_id,tweet_device)

As written, the user_id will be used as the partition key, which distributes your data around the cluster but also keeps all of the data for the same user ID on the same node. Within a single partition, unique rows are identified by the pair (tweet_id, tweet_device) and those rows will be automatically ordered by tweet_id because it is the second column listed in the primary key. (Or put another way, the first column in the PK that is not a part of the partition key determines the sort order of the partition.)

Query 1

The WHERE clause is author='Amresh'. Note that this clause does not involve any of the columns listed in the primary key; instead, it is filtering using a secondary index on author. Since the WHERE clause does not specify an exact value for the partition key column (user_id) using the index involves scanning all cluster nodes for possible matches. Results cannot be sorted when they come from more than one replica (node) because that would require holding the entire result set on the coordinator node before it could return any results to the client. The coordinator can't know what is the real "first" result row until it has confirmed that it has received and sorted every possible matching row.

If you need the information for a specific author name, separate from user ID, and sorted by tweet ID, then consider storing the data again in a different table. The data design philosophy with Cassandra is to store the data in the format you need when reading it and to actually denormalize (store redundant information) as necessary. This is because in Cassandra, writes are cheap (though it places the burden of managing multiple copies of the same logical data on the application developer).

Query 2

Here, the WHERE clause is user_id = 'xamry' which happens to be the partition key for this table. The good news is that this will go directly to the replica(s) holding this partition and not bother asking the other nodes. However, you cannot ORDER BY tweet_device because of what I explained at the top of this answer. Cassandra stores rows (within a single partition) sorted by a single column, generally the second column in the primary key. In your case, you can access data for user_id = 'xamry' ORDER BY tweet_id but not ordered by tweet_device. The answer, if you really need the data sorted by device, is the same as for Query 1: store it in a table where that is the second column in the primary key.

If, when looking up the tweets by user_id you only ever need them sorted by device, simply flip the order of the last two columns in your primary key. If you need to be able to sort either way, store the data twice in two different tables.

The Cassandra storage engine does not offer multi-column sorting other than the order of columns listed in your primary key.

Primary Key related CQL3 Queries cases & errors when sorting

Query 1

Query 2

1 Answers

Query 1

Query 2