Is there also a way to query a SASI-indexed column by exact value? I mean, it seems as if by enabling prefix or contains, that it will always query by prefix or contains. For example, if I want to query for full first name, like where their full first name really is "J" and not get "John" and "James" as well, while at other times I am indeed looking for names starting with a prefix of "Jo" for "John", "Joseph", etc.
Or, can I indeed have two indexes on a single column, one a traditional exact match, and one a prefix match. Hmmm... in which case, which gets used if I just specify a column name?
CREATE INDEX first_name_full ON mytable (first_name)...
CREATE CUSTOM INDEX first_name_prefix ON mytable (first_name)...
(I may be confused here - can you specify an index name in place of a column name in a relation in a SELECT/WHERE clause (SELECT... WHERE... first_name_exact = 'Joe')? I don't see any doc/spec that indicates that you can. I'm not sure why I thought that you could. But I don't see any code that detects and fails on this case at CREATE INDEX time. The code checks for "everything but name" rather than detecting two non-keys/values indexes on the same column.)
It would be good to have an example that illustrates this. In fact, I would argue that first and last names are perfect examples of where you really do need to query on both exact match and partial match. In fact, I'm not sure I can think of any examples of non-tokenized text fields where you don't want to reserve the ability to find an exact match even if you do need partial matches for some queries.
Will SPARSE mode in fact give me an exact match? (Sounds like it.) In which case, would I be better off with a SPARSE index for first_name_full, or would a traditional Cassandra non-custom index work fine (or even better.)
Are there any use cases of traditional Cassandra indexes which shouldn't almost automatically be converted to SPARSE. After all, the current recommended best practice is to avoid secondary indexes where the column cardinality is either very high or very low, which seems to be a match for SPARSE, although the precise meaning of SPARSE is still a bit fuzzy for me.
Maybe, for the first_name use case I mentioned the user would be better off with a first_name Materialized View using first_name in the PK instead of the SPARSE SASI index. In fact, by placing first_name in the partition key of the MV I could assure that all base table rows with the same first name would be on the same node.
If all of that is true, we will need to give users some decent guidance on when to use SPARSE SASI vs. MV (vs. classic secondary... or even DSE Search.)