16

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.

Aaron
  • 55,518
  • 11
  • 116
  • 132
mel
  • 2,730
  • 8
  • 35
  • 70

2 Answers2

17

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.

Aaron
  • 55,518
  • 11
  • 116
  • 132
  • i have a question can I query like select * from table where id = 3 and firstname = 'something' my point is it possible to have non primary key in where clause when part of primary key is provided – JustTry Jan 01 '22 at 05:46
  • @JustTry yes, it is possible to do that, as long as there is a secondary index on the non-key columns. – Aaron Jan 01 '22 at 15:03
10

There is one constraint in cassandra: any field you want to use in the where clause has to be the primary key of the table or there must be a secondary index on it. So you have to create an index to firstname and only after that you can use firstname in the where condition and you will get the result you were expecting.

VHristov
  • 1,059
  • 2
  • 13
  • 25
Helping Hand..
  • 2,430
  • 4
  • 32
  • 52
  • for id you already given primary key so no need for that. create index for firstname only. – Helping Hand.. Apr 22 '15 at 09:53
  • 1
    I created the index for firstname and now: SELECT * FROM demodb.users WHERE firstname = 'francois'; work but ELECT * FROM users WHERE firstname = 'francois' ORDER BY id DESC LIMIT 5; is still not working. – mel Apr 22 '15 at 09:56