maximum secondary indexes on a columnfamily

Question

Is it a performance issue if we have two or more secondary indexes on a columnfamily? I have orderid,city and shipmenttype. So I thought I create primary key on orderid and secondary indexes on city and shipmenttype. And use combination of secondary index columns while querying. Is that a bad modelling?

Answer 1

Consider the data that will be placed in the secondary index. Looking at the docs, you want to avoid columns with high cardinality. If your city and shipment type values vary greatly (or conversely, too similarly) then a secondary index may not be the right fit.

Look in to potentially maintaining a separate table with this information. This would behave as a manual index of sorts, but have the additional benefit of behaving as you expect a Cassandra table should. When you create or update records be sure to update this index table. Writes are cheap, performing multiple writes over the course of updating a record is not unheard of.

When looking at your access patterns will you be using the partition key as part of the WHERE clause or just the secondary indexes?

If you're performing a query against the secondary indexes along with the partition key you will achieve better performance than when you just query with secondary indexes.

For example, with WHERE orderid = 'foo' AND shipmenttype = 'bar' the request will only be sent to nodes responsible for the partition where foo is stored. Then the secondary index will be consulted for shipmenttype = 'bar' and your results will be returned.

When you run a query with just WHERE shipmenttype = 'bar' the query is sent to all nodes in the cluster before the secondary indexes are consulted for looking up rows. This is less than ideal.

Additionally should you query against multiple secondary indexes with a single request you must use ALLOW FILTERING. This will only consult ONE secondary index during your request, usually the more specific of the indexes referenced. This will cause a performance hit as all records returned from checking the first index will require checking for the other values listed in your WHERE clause.

Should you be using a secondary index always strive to include the partition key portion of the query. Secondly do NOT use multiple secondary indexes when querying a table, this will cause a major performance hit.

Ultimately your performance is determined by how you construct your queries against the partition and secondary indexes.