不支持ORDER BY与2ndary索引 [英] ORDER BY with 2ndary indexes is not supported

问题描述

我使用cassandra 2.1与最新的CQL。

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

执行此查询时：

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."

那么如何获取这些数据呢？

So how should fetch this data?

推荐答案

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

code = 2200 [无效的查询] message =ORDER BY with 2ndary不支持索引。

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

要使此查询的WHERE子句起作用，我将构建一个特定的查询表， ：

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

注意主键定义。这将创建 fromjid 和 tojid 中的分区键。虽然这将允许您在两个字段上进行查询，但也会在此表格的所有查询中指定 两个字段。但是这就是为什么他们称之为查询表，因为它通常设计为服务一个特定的查询。

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.

对于主键中的其余字段，我保持 messagedatetime 作为第一个聚簇列，以确保磁盘排序顺序。 Cassandra中的默认排序是升序，所以如果你想在查询时改变它，那就是你的 ORDER BY messagedatetime DESC 。最后，我确保 messageid 是第二个聚类列，以帮助确保主键的唯一性（假设 messageid 是唯一的）。

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;

如果您需要通过其他条件查询此数据，我强烈建议您创建额外的查询表s）。请记住，Cassandra最适合使用专门为其提供的每个查询设计的表。因为磁盘空间很便宜，操作时间不够。

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.

此外，DataStax还有一篇关于当不使用次要索引。这绝对值得一读。

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

这篇关于不支持ORDER BY与2ndary索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！