CODEWITHSUNDEEP
cassandrasql-execution-plansecondary-indexes
kisna
kisna

Reputation: 3127

how do multiple Cassandra secondary indices work?

As Cassandra does not have an execution plan, we were wondering how multiple secondary indices would work? i.e., if query was filtered by a different column order, which secondary index would get the preference and why?

We do know they are a bad practice and should be used for low cardinality sets or many duplicates but we were trying to leverage existing legacy cassandra tables and cannot use both cassandra secondary indices and SOLR indices at the same time, so don't have an option here.

Not much is discussed here either: http://www.datastax.com/docs/1.1/ddl/indexes

Upvotes: 0

Views: 432

Answers (1)

ashic
ashic

Reputation: 6495

Secondary indexes are like lookup tables you create yourself, that cassandra manages. A node stores index info for rows it contains. Updates to an index on a node and the update of the data on that node is atomic. If multiple indexes are used in your query, only one will actually be used. I hope somebody can correct me on this, but from what I can tell, the first filter in your predicate is the one that'll be used.

Don't think of indexes as global lookups (in the general case). This will lead to annoying performance problems, etc. Think of indexes as a way of quickly getting to some columns inside of a partition where the column you want an equality filter on isn't the clustering key (or you want to be able to filter on the second clustering key without specifying the first one). If you hit a partition, then index performance is usually not bad. The information about low cardinality is correct - the higher the cardinality, the worse your index will perform.

Here's a short faq on indexes: http://wiki.apache.org/cassandra/SecondaryIndexes

Upvotes: 0

Related Questions