I have heard repeatedly that secondary indexes (in cassandra) is only for convenience but not for better performance. The only case where it is recommended to use secondary indexes when you have low cardinality (such as gender column which has two values male or female)
consider this example:
CREATE TABLE users (
userID uuid,
firstname text,
lastname text,
state text,
zip int,
PRIMARY KEY (userID)
);
right now I cannot do this query unless I create a secondary index on users on firstname index
select * from users where firstname='john'
How do I denormalize this table such that I can have this query: Is this the only efficient way by using composite keys? Any other alternatives or suggestions?
CREATE TABLE users (
userID uuid,
firstname text,
lastname text,
state text,
zip int,
PRIMARY KEY (firstname,userID)
);
In order to come up with a good data model, you need to identify first ALL queries you would like to perform. If you only need to look up users by their firstname (or firstname and userID), then your second design is fine...
If you also need to look up users by their last name, then you could create another table having the same fields but a primary key on (lastname, userID). Obviously you will need to update both tables in the same time. Data duplication is fine in Cassandra.
Still, if you are concerned about the space needed for the two or more tables, you could create a single users table partitioned by user id, and additional tables for the fields you want to query by:
CREATE TABLE users (
userID uuid,
firstname text,
lastname text,
state text,
zip int,
PRIMARY KEY (userID)
);
CREATE TABLE users_by_firstname (
firstname text,
userid uuid,
PRIMARY KEY (firstname, userid)
);
The disadvantage of this solution is that you will need two queries to retrieve users by their first name:
SELECT userid FROM users_by_firstname WHERE firstname = 'Joe';
SELECT * FROM users WHERE userid IN (...);
Hope this helps