How Do I Sort a Large Result Set?

Application Scenarios

Common scenarios for sorting result sets of GeminiDB Cassandra instances:

• In a small result set, ORDER BY can be used for efficient in-memory calculations without any constraints.
• In a large result set, using ORDER BY may consume extra compute resources and make the query statement execution time out.

Solution

By default, query results of GeminiDB Cassandra instances are sorted based on the sorting rule specified during table creation. That is, when creating a table, you can determine which columns need to be sorted and how they will be sorted. In this way, the query performance can be improved. For example:

CREATE TABLE test(
    pk1 text,
    pk2 text,
    ck1 text,
    PRIMARY KEY (pk1, pk2)
) WITH CLUSTERING ORDER BY (pk1 DESC, pk2 ASC);

In the test table, the pk1 column is sorted in descending order and pk2 in ascending order. WITH CLUSTERING ORDER BY specifies the sorting order, which can be ASC (ascending) or DESC (descending), of clustering keys. If no order is specified, data is sorted in ascending order (ASC) by default.

ORDER BY Example

A small result set can be sorted using ORDER BY, for example:

• Sort the ck1 column in ascending order.

SELECT * FROM test WHERE pk1=? ORDER BY ck1 asc;

• Sorts the ck1 column in descending order.

SELECT * FROM test WHERE pk1=? ORDER BY ck1 desc;