2

I have just started working on Cassandra. I am bit confuse with the concept of secondary key.

From the definition I understood is indexing on the non key attribute of a table which is not sorted is secondary index.

So I have this table 

CREATE TABLE IF NOT EXISTS userschema.user (id int,name text, address text, company text, PRIMARY KEY (id, name))

So If I create index like this 

CREATE INDEX IF NOT EXISTS user_name_index ON userschema.user (name)

this should be secondary index.

But my requirement is to create index containing columns name , id , company.

How can I create a secondary index like this in Cassandra ?

I got this link which defines something of this short, but how come are these secondary indexes aren't they just table ?

These above user table is just the example not the actual one.

I am using Cassandra 3.0.9

user3649361
  • 944
  • 4
  • 20
  • 40
  • 1
    What queries are you going to be running against this table? The queries normally help determine the data model. – mikea Nov 20 '17 at 18:37
  • kinda as a PSA: you probably want to avoid secondary indexes here. They are made for very specific use cases (cardinality ~= number of nodes), nothing like indexes in many relational dbs people are used to. Instead create a new table and write to both. – Chris Lohfink Nov 21 '17 at 15:11

2 Answers2

1

id and name are already part of primary key.

So following queries will work

SELECT * FROM table WHERE id=1
SELECT * FROM table WHERE id=1 and name='some value'
SELECT * FROM table WHERE name='some value' ALLOW FILTERING (This is inefficeint)

You can create secondary index on company column

CREATE INDEX IF NOT EXISTS company_index ON userschema.user (company)

Now once secondary index is defined, it can be used in where clause along with primary key.

SELECT * FROM table WHERE id=1 and name='some value' and company='some value'

Though SELECT * FROM table WHERE company='some value' ALLOW FILTERING works it will be highly inefficient.

Before creating secondary index have look at When to use secondary index in cassandra

undefined_variable
  • 6,180
  • 2
  • 22
  • 37
0

The link which you have referred mainly focuses on materialized views, in which we create virtual tables to execute the queries with non-primary keys. Moreover, it seems you are creating secondary key on a Primary Key, which you have already defined in the creation of the table. Always remember that Secondary Index should be Non-Primary key.

To have a clear idea about the Secondary Indexes- Refer this https://docs.datastax.com/en/cql/3.3/cql/cql_using/useSecondaryIndex.html

Now, Pros and cons of the alternative methods for the secondary index

1.Materialized views:

It will create new virtual tables and you should run the queries in a virtual table using the old Primary keys in old and original tables and new virtual Primary keys in the new materialized table. Any changes in data modification in the original old table will be reflected at materialized table. If you drop the materialized table, but the data will be created as tombstones whose gcc_graceseconds is 864000(10 days) default. Dropping the materialized table will not have any effect on original table.

2.ALLOW FILTERING:

It is highly inefficient and is not at all advised to use allow filtering as the latencies will be high and performance will be degraded.

If you want much more information, refer this link too How do secondary indexes work in Cassandra?

Correct me if I am wrong

Chakri Stark
  • 166
  • 1
  • 1
  • 12