Can I create a secondary index on multiple columns in cassandra

Question

Can I create a secondary index on multiple columns in cassandra? like can I do

create index my_composite_index on my_column_family (id,name)

CQL throws error

:2:Bad Request: line 1:73 mismatched input ',' expecting ')'

Notorious

Answer 1

Alex's answer is correct but I thought I would add some additional input.

Cassandra secondary indexes (2i) are really meant for low-cardinality fields, i.e. things that are not unique to each entity / row.

If you have a table of 250 million US citizens, using a secondary index to track which state they're in is a perfect use case for 2i. Using a secondary index to track their social security number is not - it would create enormous performance problems for both reads and writes. You'd be better off creating your own index column family in the second scenario.

2i are not replicated and have to be created locally in each node, so there's a substantial amount of work involved in rebuilding them if you have to replace a node or add a new one.

Personally, I use 2i for filtering item results all sorted on the same CQL row (i.e. all items have the same partition key) - it's quite performant in that use case.

Westmorland

Answer 2

No. Cassandra secondary indexes are based on a single column. You can find the CQL syntax for creating a secondary index here

As a bit of background, the reason secondary indexes are based on a single column is to avoid read-before-write in order to preserve the performance of writes.

Naoma

In my firm, we have secondary indexes on multiple columns. Has it changed since the time you answered this question? – Suffering

@user911, the latest still says single column. Are you sure you're not thinking about your PRIMARY KEY definition? – Sniperscope