cassandra, select via a non primary key

Question

I'm new with cassandra and I met a problem. I created a keyspace demodb and a table users. This table got 3 columns: id (int and primary key), firstname (varchar), name (varchar). this request send me the good result:

SELECT * FROM demodb.users WHERE id = 3;

but this one:

SELECT * FROM demodb.users WHERE firstname = 'francois';

doesn't work and I get the following error message:

InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: "

This request also doesn't work:

SELECT * FROM users WHERE firstname  = 'francois'  ORDER BY id DESC LIMIT 5;
InvalidRequest: code=2200 [Invalid query] message="ORDER BY with 2ndary indexes is not supported."

Thanks in advance.

Answer 1

This request also doesn't work:

That's because you are mis-understanding how sort order works in Cassandra. Instead of using a secondary index on firstname, create a table specifically for this query, like this:

CREATE TABLE usersByFirstName (
  id int,
  firstname text,
  lastname text,
  PRIMARY KEY (firstname,id));

This query should now work:

SELECT * FROM usersByFirstName WHERE firstname='francois'
ORDER BY id DESC LIMIT 5;

Note, that I have created a compound primary key on firstname and id. This will partition your data on firstname (allowing you to query by it), while also clustering your data by id. By default, your data will be clustered by id in ascending order. To alter this behavior, you can specify a CLUSTERING ORDER in your table creation statement:

WITH CLUSTERING ORDER BY (id DESC)

...and then you won't even need an ORDER BY clause.

I recently wrote an article on how clustering order works in Cassandra (We Shall Have Order). It explains this, and covers some ordering strategies as well.