Cassandra stores columns on disk in the clustering order of your table. Therefore, to get the performance, your queries with ORDER BY should match the table's clustering order.
For example, if you often query the newest data in the table, then you should set your clustering order to be <code>(insertion_time DESC)</code>:
<code>CREATE TABLE timeseries ( event_type text, insertion_time timestamp, event blob, PRIMARY KEY (event_type, insertion_time) ) WITH CLUSTERING ORDER BY (insertion_time DESC);</code>

Match your query's ORDER BY to the clustering order

Cassandra allows you to sort your query results in reverse clustering order, but it comes at a cost. Cassandra has to read all partition rows/columns and sort them in-memory, making your queries slower and increasing load on the cluster.The slowdown is proportional to the number of rows and columns you have, and it can become a serious problem if your partitions grow to thousands of rows and dozens of columns.See more info on <a href="https://stackoverflow.com/questions/57931713/cost-of-order-by-in-cassandra">StackOverflow</a>.

Reversing clustering order at query time scans the entire partition

You can order query results to make use of the on-disk sorting of columns. You can order
 results in ascending or descending order. The ascending order will be more efficient than
 descending. If you need results in descending order, you can specify a clustering order to
 store columns on disk in the reverse order of the default. Descending queries will then be
 faster than ascending ones.The following example shows a table definition that changes the clustering order to
 descending by insertion time.<PRE><code>create table timeseries (
 event_type text,
 insertion_time timestamp,
 event blob,
 PRIMARY KEY (event_type, insertion_time)
)
WITH CLUSTERING ORDER BY (insertion_time DESC);</code></PRE>

Ordering query results to make use of the on-disk sorting of columns.

computerscience

Computer Science

You can order query results to make use of the on-disk sorting of columns. You can order results in ascending or descending order. The ascending order will be more efficient than descending. If you need results in descending order, you can specify a clustering order to store columns on disk in the reverse order of the default. Descending queries will then be faster than ascending ones.

The following example shows a table definition that changes the clustering order to descending by insertion time.

Cassandra - Clustering Order

Match your query's ORDER BY to the clustering order

Reversing clustering order at query time scans the entire partition

Ovidiu Podariu (Tech)'s ideas are part of this journey:

Related collections

Similar ideas