Apache Cassandra. Advantage and disadvantage of Secondary Index

Question

I have read, that Secondary Index in Cassandra is quite useless feature. Indeed, it makes writing to DB much more slower, you can find value only by exact index and you need to make requests to all servers in claster to find value by index. Can anyone tell me about benifit, that will be the reason to use Secondary Index?

Answer 1

Querying becomes more flexible when you add secondary indexes to table columns. You can add indexed columns to the WHERE clause of a SELECT.

When to use secondary indexes
You want to query on a column that isn't the primary key and isn't part of a composite key. The column you want to be querying on has few unique values (what I mean by this is, say you have a column Town, that is a good choice for secondary indexing because lots of people will be form the same town, date of birth however will not be such a good choice).

When to avoid secondary indexes
Try not using secondary indexes on columns contain a high count of unique values and that will produce few results.

As always, check out the documentation:

• About Indexes in Cassandra
• FAQ for Secondary Indexes