- SizeTieredCompactionStrategy: default and suitable as a starting point for most uses cases with balance of reads and writes
- LevelledCompactionStrategy: does more compaction work to improve read performance. Generally used if high ratio of reads to writes.
- DateTieredCompactionStrategy: useful for data where data is “hot” when first written but sees less access over time.
- Check that the compaction strategy is appropriately tuned (see Cassandra Docs) defaults are usually ok, but DTCS requires specific compaction options set to be effective.
Also secondary indexes on boolean columns are not effective.
See Cassandra Docs and
Does the query on the index lookup a row in a large partition?
Whole partition will be scanned to find matching rows – potentially expensive reads.