Cassandra using compound indices and secondary

Question

We want to use cassandra to store complex data
but we cannot figure out how to organize indexes.

Our table (column family) is as follows:

Users = { RandomId int, Firstname varchar, Lastname varchar, Age int, Country int, ChildCount int } 

We have queries with required fields (first name, last name, age) and additional search parameters (Country, ChildCount).
How do we organize the index to speed up this query?

At first, I thought it would be natural to create a composite index (first name, last name, age) and add a separate secondary index to the remaining fields (Country and ChildCount).
But I cannot insert rows into the table after creating the secondary indexes, and I cannot query the table.

Using

• cassandra 1.1.0
• cqlsh with the -cql3 option.

Any other suggestions on solving our problem are welcome (complex queries with mandatory and additional parameters).

Answer 1

This is my idea. You can simply create a family of columns with your RandomId as the row key and all other fields just like columns (for example, the name of the column is "firstname", the value of the column is "jonh"). After that, you need to create an additional index for each of these columns. The power of your values ​​seems low, so it should be a little efficient.

CQL code should look something like this:

 create column family users with comparator=UTF8Type and column_metadata=[{column_name: firstname, validation_class: UTF8Type,index_type: KEYS}, {column_name: lastname, validation_class: UTF8Type, index_type: KEYS}, {column_name: contry, validation_class: IntegerType, index_type: KEYS}, {column_name: age, validation_class: IntegerType, index_type: KEYS]}, {column_name: ChildCount, validation_class: IntegerType, index_type: KEYS]]; 

A good link for it could be http://www.datastax.com/docs/0.7/data_model/secondary_indexes

Let me know if I am wrong;

Answer 2

For queries with a large number of partition indexes, it is not very efficient.

I think it's better to think about query-based tables that you want to make: you need a table for queries based on the username, and this seems like the right place to store all the information regarding the user. On the other hand, you want to be able to search by country, I suggested providing a list of users: for this you do not need all the information, maybe just the first and last name or just email, etc. Then another table could do this.

This is due to data duplication, but is better suited for Cassandra data modeling ideas.

This will give:

 CREATE TABLE users( id UUID, lastname TEXT, firstname TEXT, age INT, country TEXT, childcount INT, PRIMARY KEY(UUID) ); CREATE TABLE users_by_country( country TEXT, firstname TEXT, lastname TEXT, user_uuid UUID, PRIMARY KEY((country), firstname, lastname) ); CREATE TABLE users_by_age( age INT, firstname TEXT, lastname TEXT, user_uuid UUID, PRIMARY KEY((age), firstname, lastname) );