CODEWITHSUNDEEP
cassandracqlcql3
user1955409
user1955409

Reputation: 211

Can I create a secondary index on multiple columns in cassandra

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

Upvotes: 12

Views: 16299

Answers (2)

Aaronontheweb
Aaronontheweb

Reputation: 8394

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.

Upvotes: 21

Alex Popescu
Alex Popescu

Reputation: 4002

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.

Upvotes: 13

Related Questions