Results pagination in Cassandra (CQL)

Question

I am wondering how can I achieve pagination using Cassandra.

Let us say that I have a blog. The blog lists max 10 posts per page. To access next posts a user must click on pagination menu to access page 2 (posts 11-20), page 3 (posts 21-30), etc.

Using SQL under MySQL, I could do the following:

SELECT * FROM posts LIMIT 20,10;

The first parameter of LIMIT is offset from the beginning of result set and second argument is amount of rows to fetch. The example above returns 10 rows starting from row 20.

How can I achieve the same effect in CQL?

I have found some solutions on Google, but all of them require to have "the last result from previous query". It works for having "next" button to paginate to another 10-results-set, but what if I want to jump from page 1 to page 5?

Answer 1

You don't need to use tokens, if you are using Cassandra 2.0+.

Cassandra 2.0 has auto paging. Instead of using token function to create paging, it is now a built-in feature.

Now developers can iterate over the entire result set, without having to care that it’s size is larger than the memory. As the client code iterates over the results, some extra rows can be fetched, while old ones are dropped.

Looking at this in Java, note that SELECT statement returns all rows, and the number of rows retrieved is set to 100.

I’ve shown a simple statement here, but the same code can be written with a prepared statement, couple with a bound statement. It is possible to disable automatic paging, if it is not desired. It is also important to test various fetch size settings, since you will want to keep the memorize small enough, but not so small that too many round-trips to the database are taken. Check out this blog post to see how paging works server side.

Statement stmt = new SimpleStatement(
                  "SELECT * FROM raw_weather_data"
                  + " WHERE wsid= '725474:99999'"
                    + " AND year = 2005 AND month = 6");
stmt.setFetchSize(24);
ResultSet rs = session.execute(stmt);
Iterator<Row> iter = rs.iterator();
while (!rs.isFullyFetched()) {
   rs.fetchMoreResults();
   Row row = iter.next();
   System.out.println(row);
}