CODEWITHSUNDEEP
cassandra
Cedric H.
Cedric H.

Reputation: 8298

Cassandra query with index

Imagine I have a table like this:

CREATE TABLE test(
  name  TEXT,
  tag   TEXT,
  id    UUID,
  parameter INT,
  PRIMARY KEY((name, tag, id), parameter)
);

and an index

CREATE INDEX parameter_idx on test(parameter);

With a query like this:

SELECT * FROM test WHERE name = 'name' 
                     AND tag = 'tag' 
                     AND parameter = value;

Is it correct that the index will be used, returns many partitions and that these partitions will then be filtered, thus requiring ALLOW FILTERING?

On the contrary a query like this one:

SELECT * FROM test WHERE name = 'name' 
                     AND tag = 'tag' 
                     AND id = some_id,
                     AND parameter = value;

will not use the index, a single partition will be found and only some rows corresponding to parameter = value will be returned.

Is this all correct?

Upvotes: 0

Views: 1066

Answers (1)

Jim Meyer
Jim Meyer

Reputation: 9475

You understanding sounds correct. Of course using a secondary index like that would be very inefficient. Usually it's better to design your schema so that a secondary index isn't needed for any of your queries.

Upvotes: 1

Related Questions