how does multiple indexes on low cardinality columns on cassandra works?

user_13569490 0
Viewed 43

I am trying to creating multiple indexes on my Cassandra data store. I had my schema as below

id: integer primary key
field1: text
field2: text
field3: text
field4: int

I create multiple indexes on different column

CREATE INDEX filed1_index ON mykeyspace.mytable ( field1 );
CREATE INDEX filed2_index ON mykeyspace.mytable ( field2 );
CREATE INDEX filed4_index ON mykeyspace.mytable ( field4 );

Now I tried querying the data as below

select * from mykeyspace.mytable where field1='filter1' and field2='filter2' allow filtering;

Following the doc on usage of multiple index I am not able to determine whether the index that I created above is used or not? Any pointers or explanation would be really helpful.

cql cassandra-3.0 cqlsh cassandra
1 Answers

When you create a secondary index in Cassandra, Cassandra essentially creates a corresponding hidden table for the same.

So in your case, you will have 3 hidden tables

CREATE TABLE field1_index(
    field1 text,
    key integer
    PRIMARY KEY ((field1), key) );   

CREATE TABLE field2_index(
    field2 text,
    key integer
    PRIMARY KEY ((field2), key) );   

CREATE TABLE field3_index(
    field3 text,
    key integer
    PRIMARY KEY ((field3), key) );

These tables are local to the node, hence only the data stored in that particular node is indexed.

When there are multiple indexes, Cassandra uses the index with the highest selectivity to find the rows that need to be loaded. Once the highest selectivity index is picked the rest of the predicates are filtered normally.

So for query execution, only one index can be used and the rest of the indexes are filtered normally. You can see that by observing

These queries do not require allow filtering

select * from mykeyspace.mytable where field1='filter1';
select * from mykeyspace.mytable where field2='filter2';

But a query having both indexes does

select * from mykeyspace.mytable where field1='filter1' and field2='filter2' allow filtering;

This is a good article discussing the same thing. Also, any answer about the secondary index is not complete without mentioning that always use secondary index along with partition key for maximum efficiency.

user_39210530
Related