CODEWITHSUNDEEP
cassandracassandra-3.0
Double Infinity
Double Infinity

Reputation: 255

Why Secondary Index ( = ?) and Clustering Columns (order by) CANNOT be used together for CQL Query?

EDIT: a related jira ticket

A query in pattern select * from <table> where <partition_keys> = ? and <secondary_index_column> = ? order by <first_clustering_column> desc does not work, with error msg: 

InvalidRequest: Error from server: code=2200 [Invalid query] message="ORDER BY with 2ndary indexes is not supported."

From the structure of index table, above query include the partition key, and first two cluster columns in the index table. Also, note that without order by clause, the result is sorted by clustering column as CLUSTERING ORDER.

Is there any way to make the query work? If not, why?

Upvotes: 1

Views: 433

Answers (1)

dilsingi
dilsingi

Reputation: 2958

Data in Cassandra is naturally stored based on the sort order of Clustering Columns.

Secondary index in Cassandra is way different than a corresponding index in relation database. Its local per node, which means that its contents aren't known to other nodes of the cluster. So sorting by this index is highly impossible. Also within the node, the secondary indexes are holding just pointers to corresponding partition key.

If you need sorting to be performed by Cassandra, have them as clustering columns. Otherwise you can sort them in code, after you retrieve the results.

Also secondary indexes aren't ideal for Cassandra and definitely a better model is to not have them in first place, to save some headache for future.

Upvotes: 3

Related Questions