How does consistency level relate to Cassandra native secondary index?

Question

How does consistency level relate to secondary index? If e.g. querying with QUORUM in a secondary index query with no limit? Cassandra internal: First query all nodes local indexes and then with result (partitions) from local index query, use QUORUM to get the indexed partitions data?

And that's why the following statement is true?:

Q: "How does choice of Consistency Level affect cluster availability when using secondary indexes?"

A: "Because secondary indexes are distributed, you must have CL nodes available for ALL token ranges in the cluster in order to complete a query. For example, with RF = 3, when two out of three consecutive nodes in the ring are unavailable, all secondary index queries at CL = QUORUM will fail, however secondary index queries at CL = ONE will succeed. This is true regardless of cluster size."

Answer 1

The consistency level you've specified determines how many replicas must be available for the read request to succeed.

When executing a query that requires the secondary index, the same applies not just for a quorum of nodes but this time for a quorum of all the token ranges.

For example, a cluster with 6 nodes A to F. If node A owns token range 50 to 100, and nodes B and C are adjacent nodes to the right of the ring, then for a read query with a consistency of QUORUM 2 of nodes A/B/C must be available for index lookups of token range 50-100. This requirement is repeated for all the other token ranges in the DC/ring.

Secondary index lookups are very expensive for this reason so we recommend you avoid them and denormalise where possible if performance matters to you. Cheers!