How is Cassandra secondary index suitable for both low cardinality and unique columns?

Question

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,

1. I can have one Secondary Index on email
2. 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

1. 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)

2. 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.

3. 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.

Answer 1

The statements you quoted are not quite correct. Note that since you haven't provided a link or reference to your source, I don't have the full information and so there's a good chance you've taken it out of context.

It would be more appropriate to say "not-so-low cardinality" instead of just "low" because columns which have a very low cardinality end up with indices which have very large partitions. For example, a column with boolean values only have 2 partitions (TRUE and FALSE) so each would have way too many rows. Similarly, a [binary] gender column would only have MALE/FEMALE or BOY/GIRL.

But even columns with not-so-low cardinality can also be problematic. Say you have a very popular mobile app used around the world. If you index the country column of the users table, country=AU (Australia) might not have so many [rows of] users but India or China can potentially have tens or hundreds of millions.

Similarly, indexing very high cardinality columns can be an issue depending on the access pattern because it is not efficient to do index lookups on all nodes to return a very low number of rows, or in some cases just one row. Email is a good example of this -- each email will be used by at most just one user.

If your app will seldom do index lookups (such as filtering by email) then it is probably okay. Otherwise, we recommend creating another table partitioned by email (like users_by_email) if performance matters.

The general recommendation is to avoid using secondary indexes. Design a table for each application query for optimum read performance. Cheers!