How to filter Cassandra rows by two columns

Question

I have the following table

CREATE TABLE detections.events (
    id UUID,
    event_type INT,
    created_time TIMESTAMP
    PRIMARY KEY ( id, event_type, created_time )
) WITH CLUSTERING ORDER BY ( created_time DESC );

and 2 queries:

SELECT * FROM detections.events WHERE id=?

SELECT * FROM detections.events WHERE event_type=?

but, apparently, i'm unable to set up a table like that, if I do PRIMARY KEY ( id, created_time ) I can query by id, but, not by event_type

If I do PRIMARY KEY ( event_type, created_time ) I can query by event_type, but, not by id

There's a way that I can model my table to be able to accomplish both queries without creating a new table and still order by created_time?

Answer 1

I just solved that by using

PRIMARY KEY ( id, created_time )

CREATE INDEX event_type_idx ON detections.events( event_type );

more info: https://docs.datastax.com/en/cql-oss/3.3/cql/cql_using/useMultIndexes.html