What Should I Use

3 min to complete

A guideline on what to use and when.
A rule of thumb:
1. For no storage overhead, filtering is the only option
2. For highly selective queries, indexing may be more beneficial than filtering
3. If most of the queries specify a single partition key:
* a local index is faster than a global index, especially with token aware load balancing policy
* filtering a single partition may be faster than an index, depending on the partition size
4. If most of the queries are multi-partition, local indexes won’t help:
* a global index may be beneficial, especially if query selectivity is expected to be high
* filtering may be better if the query selectivity is low (i.e. the result consists of most of the rows)

One could often see that filtering is just as fast (or even way faster) for reading as indexes, especially if we’re interested in the majority of the results. For example, if the query is going to return 60% of all rows of a table anyway, or if we just query a single partition and it’s small, filtering can get orders of magnitude faster than an index, because it simply reads all the data sequentially instead of a series of random reads that indexes need.