I am new to nosql database such as Cassandra, and currently I saw this secondary index and sstable attached secondary index. Some how I am confused about the purpose to use secondary index in Cassandra, what's the difference between relational database and Cassandra with secondary index ? Which one has better performance when being queried ?
In Cassandra :
You should use SASI if:
- you need multi criteria search and you don't need ordering/grouping/scoring
- you mostly need 100 to 1000 of rows for your search queries
- you always know the partition keys of the rows to be searched for (this one applies to native secondary index too)
- you want to index static columns (SASI has no penalty since it indexes the whole partition)
You should avoid SASI if:
- you have very wide partitions to index, SASI only give the partition offset. The expensive linear scanning is still performed on Cassandra side, without the help of clustering column index for skipping blocks
- you have strong SLA on search latency, for example sub-second requirement
- you need search for analytics scenarios (SASI is not the right fit to fetch half of your table) unless you use SASI with co-located Apache Spark but even in this case, search engines win with 2 orders of magnitude for latency
ordering of the search results is important for you
If you decide to try SASI in production, please keep in mind that SASI does impact your write/flush throughput, compaction throughput as well as repair and streaming operations. It is quite expected because SASI index files follow SSTable life-cycle.
Source : http://www.doanduyhai.com/blog/?p=2058
In RDBMS : There is no restriction like this. you can order your result, perform aggregation and grouping.