CODEWITHSUNDEEP
cassandra
gorgonauta
gorgonauta

Reputation: 291

Cassandra seconday index vs materialized view

I'm modeling my table for Cassandra 3.0+. The objective is to build a table that store user's activities, here what i've done so far: (userid come from another database Mysql)

CREATE TABLE activity (
    userid int,
    type int,
    remoteid text,
    time timestamp,
    imported timestamp,
    visibility int,
    title text,
    description text,
    img text,
    customfields MAP<text,text>,
PRIMARY KEY (userid, type, remoteid, time, imported))

This are the main queries that i use:

SELECT * FROM activity WHERE userid = ? AND remoteid = ?;
SELECT * FROM activity WHERE userid = ? AND type = ? AND LIMIT 10;

Now i need to add the column visibility on the second query. So, from what i've learned around, i can choose between a secondary index or a materialized view. This are the facts:

So what should i choose? materialized view or index? I know that index with low cardinality are bad choice, but my query include always the partition key and a limit, so maybe is not that bad.

Upvotes: 14

Views: 6500

Answers (1)

HerberthObregon
HerberthObregon

Reputation: 2131

If you are always going to use the partition key I recommend using secondary indexes.

Materialized views are better when you do not know the partition key

References:

Principal Article!

• Cassandra Secondary Index Preview #1

Here is a comparison with the Materialized Views and the secondary indices

• Materialized View Performance in Cassandra 3.x

And here is where the PK is known is more effective to use an index

• Cassandra Native Secondary Index Deep Dive

Upvotes: 13

Related Questions