Performance consideration of Cassandra query using composite partition key vs clustering column

Question

I have the following DDL:

CREATE TABLE mykeyspace.mytable (
a text,
b text,
c text,
d text,
e text,
starttime timestamp,
endtime timestamp,
PRIMARY KEY ((a, b, c), d, e, starttime, endtime)
) WITH CLUSTERING ORDER BY (d ASC, e ASC, starttime ASC, endtime ASC)

and I only have the following SELECT/DELETE query:

SELECT */DELETE FROM mytable WHERE a = ? AND b = ? AND C = ? AND d = ?;

I just wonder if the column d can be included as part of the composite partition key so a row lookup is enough instead of a row lookup + clustering column lookup? In this case it will improve performance as well?

OK I will clarify that d is part of clustering column and not PARTITION key. I want to make it as part of the composite PARTITION key. — user892960

Ashraful Islam Ashraful Islam · Accepted Answer · 2017-05-19T06:09:10

The column d include in the composite partition key will absolutely improve performance

Your data will distribute well among the cluster.
Your SELECT query will be faster, no clustering level filtering is required
Your DELETE query will mark that partition as markedForDeleteAt, instead of inserting range tombstone

Performance consideration of Cassandra query using composite partition key vs clustering column

2 Answers