performance of using primary key and secondary index in a query in cassandra

3
votes

Lets say there is a table with 3 columns A,B, and C. A is primary key. I have 2 types of query, one that searches by A and B and another that searches by A and C. Is it better to add a secondary index for C to search based on A and C or make a new table with A, C, and B columns.

To put it in different perspective, in general it is a bad idea to have two secondary indexes on two columns and have a where clause conditioning on both indexes. Is it the same case for combining primary key and a secondary index?

https://www.youtube.com/watch?v=CbeRmb8fI9s#t=56

https://www.youtube.com/watch?v=N6UY1y3dgAk#t=30

1
cassandraprimary-keysecondary-indexes

1 Answers

5
votes

Secondary indexes almost never aid performance, they are mostly a tool of convince for allowing queries to explore your data. Almost all performance gains come from properly structuring your primary key and creating data schemas which properly model the queries you want to perform.

So having two tables A by B and A by C would most likely be the ideal solution and will actually scale with your data.