ORDER BY with 2ndary indexes is not supported

Question

I am using cassandra 2.1 with latest CQL.

Here is my table & indexes:

CREATE TABLE mydata.chats_new (
    id bigint,
    adid bigint,
    fromdemail text,
    fromemail text,
    fromjid text,
    messagebody text,
    messagedatetime text,
    messageid text,
    messagetype text,
    todemail text,
    toemail text,
    tojid text,
    PRIMARY KEY(messageid,messagedatetime)
);



CREATE INDEX user_fromJid ON mydata.chats_new (fromjid);
CREATE INDEX user_toJid ON mydata.chats_new (tojid);
CREATE INDEX user_adid ON mydata.chats_new (adid);

When i execute this query:

select * from chats_new WHERE fromjid='test' AND toJid='test1' ORDER BY messagedatetime DESC;

I got this error:

code=2200 [Invalid query] message="ORDER BY with 2ndary indexes is not supported."

So how should fetch this data?

Answer 1

select * from chats_new 
WHERE fromjid='test' AND toJid='test1' 
ORDER BY messagedatetime DESC;

code=2200 [Invalid query] message="ORDER BY with 2ndary indexes is not supported."

To get the WHERE clause of this query to work, I would build a specific query table, like this:

CREATE TABLE mydata.chats_new_by_fromjid_and_tojid (
    id bigint,
    adid bigint,
    fromdemail text,
    fromemail text,
    fromjid text,
    messagebody text,
    messagedatetime text,
    messageid text,
    messagetype text,
    todemail text,
    toemail text,
    tojid text,
    PRIMARY KEY((fromjid, tojid), messagedatetime, messageid)
);

Note the primary key definition. This creates a partitioning key out of fromjid and tojid. While this will allow you to query on both fields, it will also require both fields to be specified in all queries on this table. But that's why they call it a "query table", as it is generally designed to serve one particular query.

As for the remaining fields in the primary key, I kept messagedatetime as the first clustering column, to assure on-disk sort order. Default ordering in Cassandra is ascending, so if you want to change that at query time, that's where your ORDER BY messagedatetime DESC comes into play. And lastly, I made sure that the messageid was the second clustering column, to help ensure primary key uniqueness (assuming that messageid is unique).

Now, this query will work:

select * from chats_new_by_fromjid_and_tojid 
WHERE fromjid='test' AND toJid='test1'
ORDER BY messagedatetime DESC;

If you need to query this data by additional criteria, I highly recommend that you create additional query table(s). Remember, Cassandra works best with tables that are specifically designed for each query they serve. It's ok to replicate your data a few times, because disk space is cheap...operation time is not.

Also, DataStax has a great article on when not to use a secondary index. It's definitely worth a read.