I have asked this question before, and have received apparently conflicting answers from various sources, including documentation. So I am repeating it with links to documentation and what some people at Datastax have said, in the hopes that a developer of Cassandra (or someone with real Cassandra 1.1 experience) can clear this up for me.
I wish to run queries as follows, where ID is the PK, and Data is the final "value":
SELECT Data FROM MyApp.ProductFamilies WHERE ID IN (?, ?, ?) AND PriceLow >= ?
AND PriceHigh <= ? AND MassLow >= ? AND MassHigh <= ? and MnfGeo >= ? AND
MnfGeo <= ?
Note that under the hood, CQL is filtering the column names, which are the values. I'm not filtering the final "value", and hence need no secondary index.
This documentation states the following:
WHERE clauses can include greater-than and less-than comparisons on columns other than the first. As long as all previous key-component columns have already been identified with strict = comparisons, the last given key component column can be any sort of comparison.
Per this, the above query would work fine, and @jbellis confirmed this, but others said no because of the PK arrangement.
The statement is a bit vague because column 1 may or may not be the full PK, hence is not intrinsically significant. I interpret it to read:
- You can <=> query all non PK columns.
- If you have a composite key, and you have an = on each component except the final one, you can <=> query the last component.
However, per the following, one cannot apply a WHERE clause on a column that is not part of the PK (see here).
The problem with doing a CONTAINS without an index is that this is very inefficient in general, and forbidding such generally inefficient query is something we want with Cassandra/CQL (much like the fact that we don’t allow ‘WHERE a = 3′ unless a is either part of the PK or is indexed).
Per this, we can apply a WHERE predicate only to PK components. This means my query won't work.
What am I missing?
