CODEWITHSUNDEEP
cassandracql
hurelhuyag
hurelhuyag

Reputation: 1753

How it is ordered if I query by secondary index?

I created this table on cassandra.

CREATE TABLE user_event(
    userId bigint,
    type varchar,
    createdAt timestamp,
    PRIMARY KEY ((userId), createdAt)
) WITH CLUSTERING ORDER BY (createdAt DESC);
CREATE INDEX user_event_type ON user_event(type);

If I query by userId query result will be ordered by createdAt column.

SELECT * FROM user_event WHERE userId = 1;

But how it is ordered if I query by type? Can I get last SIGN_IN event?

SELECT * FROM user_event WHERE userId = 1 AND type = 'SIGN_IN' LIMIT 1;

Is there any guarantee that result is ordered by createdAt?

Upvotes: 1

Views: 46

Answers (1)

Aaron
Aaron

Reputation: 57758

The key to understanding this scenario, is to remember that result set order can only be enforced within a partition. As you are still querying by partition key (userId) all data within each partition will still be ordered by createdAt (DESCending).

"Guarantee" is a strong word, and one that I am hesitant to use. The results queried in this way should maintain their on-disk sort order. I would definitely test it out. But as long as you provide userId as a part of the query, the results should be returned sorted by createdAt.

Upvotes: 1

Related Questions