Cassandra ignores indexes in UPDATE CQL statement

1
votes

I'm using Cassandra 1.2.25 with cqlsh and all CQL statements are CQL3. I'm creating a CF and after that add a secondary index on a column. After I'm adding the secondary index (as explained here : http://www.datastax.com/docs/1.1/ddl/indexes) I would expect UPDATE statements to be able to use the secondary index column in the WHERE clause. Sadly they don't.

  1. Created the table schema :

    CREATE TABLE registry ( referencesetid uuid, referenceid uuid, acquiredbyendpoint uuid, name text, PRIMARY KEY (referencesetid, referenceid) )

  2. Added the index :

    CREATE INDEX nameidx ON registry (name);

  3. Inserted one row :

    INSERT INTO registry (referenceSetId,referenceId,acquiredByEndpoint,name) VALUES(96ade698-09c5-46f0-beb4-b842e5352bc8,a97c6163-f4bc-4eaf-a7e0-700578bdc44c,00000000-0000-0000-0000-000000000000,'John');

  4. Executing update query (In the WHERE clause I'm referring to the nameidx as this is the name of the indexed field 'name') :

    UPDATE registry SET acquiredbyendpoint=00000000-0000-0000-0000-000000000001 WHERE nameidx='John' AND referencesetid=36da89c3-f256-4d56-83c0-37569311a8a0;

  5. Error :

    Bad Request: Unknown key identifier nameidx

Does anyone has any suggestions what I'm doing wrong?

It might be something less obvious (ie. config files, or C* version) as it the C* indexes documentation seems to me straightforward.

Cheers Paul.

1
cassandra

1 Answers

4
votes

As you say at the top, Cassandra doesn't support UPDATE with a field in the WHERE clause being an indexed field. You can only do UPDATE with primary key fields.

Also, the field is still called name, the index names aren't used in index lookups.