卡桑德拉复合键上的二级索引

Question

I have this table in cassandra

CREATE TABLE global_product_highlights (
  deal_id text,
  product_id text,
  highlight_strength double,
  category_id text,
  creation_date timestamp,
  rank int,
  PRIMARY KEY (deal_id, product_id, highlight_strength)
)

When i fire below query in Golang

err = session.Query("select product_id from global_product_highlights where category_id=? order by highlight_strength DESC",default_category).Scan(&prodId_array)

I get ERROR : ORDER BY with 2ndary indexes is not supported.

I have an index on category_id.

I don't completely understand how is secondary index applied on composite keys in cassandra.

Appreciate if anyone would explain and rectify this one.

Answer 1

The ORDER BY clause in Cassandra only works on your first clustering column (2nd column in the primary key), which in this case is your product_id. This DataStax doc states that:

Querying compound primary keys and sorting results ORDER BY clauses can select a single column only. That column has to be the second column in a compound PRIMARY KEY.

So, if you want to have your table sorted by highlight_strength, then you'll need to make that field the first clustering column.