[CASSANDRA-7630] compound secondary index on primary key fields

• Type: Improvement
• Status: Open
• Priority: Normal
• Resolution: Unresolved
• Component/s: Feature/2i Index
• Labels: compositeColumns secondaryIndex secondary_index
• Assignee: Unassigned
• Reporter: Carlo Bertuccini
• Created: 26/Jul/14 08:19
• Updated: 16/Apr/19 09:31

Description

Hi all,
I was wondering if a secondary index could be composite by more columns which are parts of the primary key.

Example

CREATE TABLE users (
  firstname text,
  lastname text,
  email text,
  age int,
  city text,
  state text,
  PRIMARY KEY ((firstname), email, lastname)
)

This index should now be possible

CREATE INDEX surname_by_city on (city, lastname);

Since we surely know that lastname is always populated.

Now the challenge is (and in my mind looks possible!-) to perform such queries:

select * from users where city = 'seattle';
select * from users where city = 'seattle' and lastname = 'kirkland';
select * from users where firstname='john' and city = 'seattle';
select * from users where firstname='john' and city = 'seattle' and lastname = 'kirkland';

If this possibility has already been evaluated and discarded/being developed I ask sorry but I couldn't find anywhere.

Regards,
Carlo