How to convert complex sql to equivalent Cassandra view

Question

Consider a table with the following table structure ...

CREATE TABLE table1 (id integer, name varchar (32), ts timestamp);

SELECT * FROM table1 WHERE id = xx and ts between '2010/04/01' and '2010/04/11'

Given this query, how would you present this dataset using Cassandra?

Answer 1

create an index on either id or ts (whichever is more selective), query it and filter the other part manually.

(This is basically what the sql database does under the hood, by the way.)

Answer 2

You need to create a secondary index. One of the Cassandra index templates described on my blog will solve your problem.

http://pkghosh.wordpress.com/2011/03/02/cassandra-secondary-index-patterns/

Pranab