How do I get the last 50 rows for this table using Cassandra CQL?

Question

This is the query I used to create the table:

CREATE TABLE test.comments (msguuid timeuuid, page text, userid text, username text, msg text, timestamp int, PRIMARY KEY (msguuid));

I want to get the last 50 rows sorted by timestamp in descending order.

If I try something like: SELECT * FROM test.comments WHERE page = 'test' AND timestamp < 1496468332, I get this error:

Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

I'd prefer not to use allow filtering, I want the query to be as quick as possible.

I've looked at another stackoverflow question here Cassandra cql: how to select the LAST n rows from a table and tried the solution:

CREATE TABLE test.comments (msguuid timeuuid, page text, userid text, username text, msg text, timestamp int, PRIMARY KEY (msguuid)) WITH CLUSTERING ORDER BY (msguuid DESC);

But then I get this error: InvalidRequest: Error from server: code=2200 [Invalid query] message="Only clustering key columns can be defined in CLUSTERING ORDER directive"

I'm new to Cassandra, so forgive me if this has an obvious answer. I just can't seem to get it to work.

I would highly appreciate if anyone could help me.

Answer 1

Instead of using index create a Materialized View

Create a materialized view with page as partition key and msguuid as clustering key order by desc.

CREATE MATERIALIZED VIEW test.comments_by_page AS
    SELECT *
    FROM test.comments
    WHERE page IS NOT NULL AND msguuid IS NOT NULL
    PRIMARY KEY (page, msguuid)
    WITH CLUSTERING ORDER BY (msguuid DESC);

Though you are using msguuid as current timestamp's timeuuid, you data will sorted by time desc.

To get last 50 row of a page use the below query :

SELECT * FROM comments_by_page WHERE page = 'test' LIMIT 50;

Check this link to know the performance of Materialized View over Index and when not to use : http://www.datastax.com/dev/blog/materialized-view-performance-in-cassandra-3-x

Answer 2

In cassandra world try to model your table based on the queries it needs to satisfy. If the query always goes by where clause "page" and msguuid is only there for uniqueness redesign the table to something like as follows

CREATE TABLE test.comments (msguuid timeuuid, page text, userid text, username text, msg text, timestamp int, PRIMARY KEY (page, msguuid), WITH CLUSTERING ORDER BY (msguuid DESC));

Now the table is naturally order by msguuid and there isn't any requirement to have additional overhead of Materialized View.