Can I create a secondary index on multiple columns in cassandra

Question

Is it possible to create a secondary index for several columns in cassandra? how can i do

create my_composite_index index on my_column_family (id, name)

CQL throws an error

: 2: Invalid request: line 1:73 inconsistent input ',' Waiting ')'

Answer 1

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

The secondary Cassandra (2i) indices are indeed intended for low power fields, i.e. things that are not unique to each entity / row.

If you have a spreadsheet of 250 million US citizens, using a secondary index to track the state they are in is an ideal use case for 2i. Using a secondary index to track social security numbers is not - it will create huge performance problems for reading and writing. You better create your own family of indexed columns in the second scenario.

2i are not replicated and must be created locally in each node, therefore, substantial work is required to restore them if you need to replace the node or add a new one.

Personally, I use 2i to filter the results of elements sorted by the same CQL string (i.e. all elements have the same section key) - it is quite effective in this case.

Answer 2

Not. Cassandra secondary indexes are based on a single column. CQL syntax can be found to create a secondary index here

As a secondary reason, secondary indexes are based on a single column to avoid reading before writing, in order to maintain write performance.