Can the first key of partition key be a clustering key?

Question

With the below schema in Cassandra database:

CREATE TABLE IF NOT EXISTS  stackoverflow_sorted_by_key_part_two (
      key_part_one      UUID,
      key_part_two      UUID,
      idempotence_key   int,
      data              TEXT,
      PRIMARY KEY((key_part_one, idempotence_key), key_part_two)      
);

(key_part_one, idempotence_key) is a partition key & key_part_two is clustering key.

With the above schema, records are sorted(as part of table indexing) order by key_part_two in ascending order

But, for faster select query(with WHERE key_part_one == "some_uuid"), we want the records sorted(as part of table indexing) order by key_part_one.

Does the below schema enhance the select query performance?

CREATE TABLE IF NOT EXISTS  stackoverflow_sorted_by_key_part_one (
      key_part_one      UUID,
      key_part_two      UUID,
      idempotence_key   int,
      data              TEXT,
      PRIMARY KEY((key_part_one, idempotence_key), key_part_two)      
)WITH CLUSTERING ORDER BY (key_part_one ASC);

Alex Ott Alex Ott · Accepted Answer · 2021-04-11T15:28:36

No, you can't do that - you can specify only clustering columns in the WITH CLUSTERING ORDER BY. If column is part of partition key, it's not sortable - Cassandra applies the hash function to the value of this column (or columns if it's composite key), and use that hash (token) to find what node is responsible for that token. And sorting by clustering column then happens inside the partition only.

P.S. Look through the first chapters of the "Cassandra: The definitive guide, 3rd edition" book that is freely available from DataStax site - it will help you with data modeling for Cassandra.

Can the first key of partition key be a clustering key?

1 Answers