Cassandra indexes vs materialized view

Question

I have next Cassandra table structure:

CREATE TABLE ringostat.hits (
  hitId uuid,
  clientId VARCHAR,
  session MAP<VARCHAR, TEXT>,
  traffic MAP<VARCHAR, TEXT>,
  PRIMARY KEY (hitId, clientId)
);

INSERT INTO ringostat.hits (hitId, clientId, session, traffic)
  VALUES('550e8400-e29b-41d4-a716-446655440000'. 'clientId', {'id': '1', 'number': '1', 'startTime': '1460023732', 'endTime': '1460023762'}, {'referralPath': '/example_path_for_example', 'campaign': '(not set)', 'source': 'www.google.com', 'medium': 'referal', 'keyword': '(not set)', 'adContent': '(not set)', 'campaignId': '', 'gclid': '', 'yclid': ''});

INSERT INTO ringostat.hits (hitId, clientId, session, traffic)
      VALUES('650e8400-e29b-41d4-a716-446655440000'. 'clientId', {'id': '1', 'number': '1', 'startTime': '1460023732', 'endTime': '1460023762'}, {'referralPath': '/example_path_for_example', 'campaign': '(not set)', 'source': 'www.google.com', 'medium': 'cpc', 'keyword': '(not set)', 'adContent': '(not set)', 'campaignId': '', 'gclid': '', 'yclid': ''});

INSERT INTO ringostat.hits (hitId, clientId, session, traffic)
      VALUES('750e8400-e29b-41d4-a716-446655440000'. 'clientId', {'id': '1', 'number': '1', 'startTime': '1460023732', 'endTime': '1460023762'}, {'referralPath': '/example_path_for_example', 'campaign': '(not set)', 'source': 'www.google.com', 'medium': 'referal', 'keyword': '(not set)', 'adContent': '(not set)', 'campaignId': '', 'gclid': '', 'yclid': ''});

I want to select all rows where source='www.google.com' AND medium='referal'.

SELECT * FROM hits WHERE traffic['source'] = 'www.google.com' AND traffic['medium'] = 'referal' ALLOW FILTERING;

Without add ALLOW FILTERING I got error: No supported secondary index found for the non primary key columns restrictions.

That's why I see two options:

Create index on traffic column.
Create materialized view.
Create another table and set INDEX for traffic column.

Which is the best option ? Also, I have many fields with MAP type on which I will need to filter. What issues can be if on every field I will add INDEX ?

Thank You.

You should consider asking your final question as a separate question. — OrangeDog

OrangeDog OrangeDog · Accepted Answer · 2016-04-07T12:52:02

From When to use an index.

Do not use an index in these situations:

On high-cardinality columns because you then query a huge volume of records for a small number of results. [...] Conversely, creating an index on an extremely low-cardinality column, such as a boolean column, does not make sense.

In tables that use a counter column

On a frequently updated or deleted column.

To look for a row in a large partition unless narrowly queried.

If your planned usage meets one or more of these criteria, it is probably better to use a materialized view.

Cassandra indexes vs materialized view

1 Answers