ORDER BY with second indices is not supported
I am using cassandra 2.1 with latest CQL.
Here is my table and 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 request:
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 do you get this data?
source to share
select * from chats_new
WHERE fromjid='test' AND toJid='test1'
ORDER BY messagedatetime DESC;
code = 2200 [Invalid query] message = "ORDER BY with second indices is not supported."
To make the WHERE clause of this query work, I would build a specific query table, for example:
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)
);
Pay attention to the definition of the primary key. This creates a split key from fromjid
and tojid
. While this will allow you to query both fields, both queries in this table will also require you to specify both fields. But why do they call it a "query table" because it is usually designed to serve one particular query.
For the rest of the primary key fields, I kept it messagedatetime
as the first clustering column to ensure the sort order on disk. The default ordering in Cassandra is up, so if you want to change that at the time of the request, that's when yours ORDER BY messagedatetime DESC
comes into play. And finally, I made sure to be the messageid
second clustering column to enforce the uniqueness of the primary key (assuming it 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 using additional criteria, I highly recommend that you create an additional query table. Remember, Cassandra works best with tables that are specifically tailored for each request they serve. It's okay to replicate your data multiple times because disk space is cheap ... uptime isn't.
Also, DataStax has a great article on if not using a secondary index . This is definitely worth reading.
source to share