Search code examples
yugabytedb

Does sequence of clustering keys matter when querying in YugabyteDB YCQL

[Question posted by a user on YugabyteDB Community Slack]

I have a question regarding clustering column order in YCQL. Below is the PRIMARY key definition:

 PRIMARY KEY (fsym_id, item, fiscalperiodenddate, startdate)
) WITH CLUSTERING ORDER BY (item ASC, fiscalperiodenddate DESC, startdate DESC).

Does sequence of clustering key matter when they try to query? For example:

select * from factsetestimatev4.con_af where fsym_id = 'Q2YN1N-R' and item='EPS' and startdate<='2022-05-03' and enddate>='2022-05-03' and fiscalperiodenddate>='2022-12-31';

VS

select * from factsetestimatev4.con_af where fsym_id = 'Q2YN1N-R' and item='EPS' and fiscalperiodenddate>='2022-12-31' and startdate<='2022-04-04' and enddate>='2022-04-04';

As per my understanding, it should not matter.

Solution

  • The order of predicates in the query doesn't matter. The examples below are the same:

    WHERE cond1 and cond2

    Vs

    WHERE cond2 and cond1

    But a good choice of PRIMARY KEY matters independent of the above point.

    Alo, enddate is not part of PK.. so the app could have some inefficiency there because it might have to scan everything that's startdate < Apr/04th.. for the provided fsym_id, item, fiscalperiodenddate.