CODEWITHSUNDEEP
cassandracolumnstorenosql
Cortlendt
Cortlendt

Reputation: 2240

ORDER BY over secondary index in Cassandra

I'm evaluating Cassandra usage for a solution which will need to query a Partition Key and get top 100 results sorted by priority column, which will not be in Clustered Key. I only query by shard key.

CREATE TABLE my_table (
shard_key int,
enity_id int,
priority int, 
PRIMARY KEY ((shard_key), entity_id)
);
CREATE INDEX prio ON my_table (priority);

Can I write an efficient CQL query like

SELECT * FROM my_table WHERE shard_key=1 ORDER BY priority LIMIT 100

Is it possible in Cassandra or should I look elsewhere?

Upvotes: 3

Views: 1240

Answers (2)

Andrés de la Peña
Andrés de la Peña

Reputation: 331

As an alternative solution, you may want to consider Stratio’s Lucene indexes to order by any indexed column.

Upvotes: 0

Ashraful Islam
Ashraful Islam

Reputation: 12840

In Cassandra 3.0 introduced Materialized View
If you are using cassandra 3.0 or upper version, you can use Materialized view to order by non primary key

Create a Materialized view like this one : 

CREATE MATERIALIZED VIEW my_table_view AS
    SELECT shard_key, priority, entity_id
    FROM my_table
    WHERE shard_key IS NOT NULL AND priority IS NOT NULL AND entity_id IS NOT NULL
    PRIMARY KEY (shard_key, priority, entity_id);

Suppose you have these data on my_table : 

 shard_key | entity_id | priority
-----------+-----------+----------
         1 |        10 |      100
         1 |        11 |      101
         1 |        12 |      102
         1 |        13 |      103
         2 |        20 |      200

Now you can query like this one : 

SELECT * FROM my_table_view WHERE shard_key = 1 ORDER BY priority ASC LIMIT 100;

The output will be : 

 shard_key | priority | entity_id
-----------+----------+-----------
         1 |      100 |        10
         1 |      101 |        11
         1 |      102 |        12
         1 |      103 |        13

Upvotes: 1

Related Questions