The Cassandra documentation states,
Do not use an index in these situations:
- On high-cardinality columns because you then query a huge volume of records for a small number of results. See Problems using a high-cardinality column index below.
It goes on,
If you create an index on a high-cardinality column, which has many distinct values, a query between the fields will incur many seeks for very few results. In the table with a billion songs, looking up songs by writer (a value that is typically unique for each song) instead of by their artist, is likely to be very inefficient. It would probably be more efficient to manually maintain the table as a form of an index instead of using the Cassandra built-in index. For columns containing unique data, it is sometimes fine performance-wise to use an index for convenience, as long as the query volume to the table having an indexed column is moderate and not under constant load.
But never really answers the question: why is it inefficient? I have no idea what "manually maintaining the table as a form of an index" means. But then it somewhat contradicts itself with "…it is sometimes fine performance-wise to use an index for convenience as long as the query volume is moderate…"
Is this just trying to tell me to use the PK when and where I can? What's the inefficiency? My understanding is that a query that would hit an index would need to query every¹ node in the cluster, and then each node would do a lookup in its local index and the results would then get aggregated. This is not necessarily expensive (each index lookup should be fairly cheap) except that we pay in network latency, as we must wait for the slowest node of the lot. Am I missing anything here?
But if I have a collection that has a bajillion items that — on rare occasion — needs to be looked up by a different but almost unique attribute … this is an appropriate use, right?
¹Every? IDK if replication means that this can hit 1/3 of the cluster for a replication factor of 3 or not?