I am "Still" reading a book on Cassandra where it says the following
Secondary indexes are best suited for low-cardinality columns, that is, columns that contain the same value for many rows. An example might be a "location" column on the users table; .............If we wanted to be able to answer questions such as Who are all of the users that live in New York?, that index would be quite useful.
And then immediately afterwards....
Secondary indexes can also be used for columns whose values are unique, such as the email column in the users table. If, for instance, we wanted to build a forgot password feature in which the user enters their e-mail address, we'd be able to use an index on email to look up the user's record.
If I can parse that, it is saying "Better suited for Low-Cardinality Columns" but can also be good for "Medium-High Cardinality Columns". In other words,
- I can have one Secondary Index on email
- I can have another secondary index on location
So they both can help, but which one is the better use case for secondary index in Cassandra domain?
I kind of understood it "Later" this way
Secondary Index, Materialized Views, and Denormalization is just a way to manage the access of data when/how we would need them. But they come with their own caveats (like anything would)
Secondary index has 2 way lookups (first get the primary keys and then seek the partitions) - so that's why low cardinality data access is the best balance when using 2nd Index. Otherwise, it's not recommended.
Materialized views have performance issues since it needs to balance write throughput with base and view data + eventual consistency.
So it's again classically, no "Silver Bullet" type of situation.