data modeling - CQL: Search a table in cassandra using '<' on a indexed column -

my cassandra data model:

    create table last_activity_tracker ( id uuid, recent_activity_time timestamp, primary key(id));     create index activity_idx on last_activity_tracker (recent_activity_time) ; 

the idea keep track of 'id's , recent activity of event.

i need find 'id's last activity year ago. so, tried:

    select * last_activity_tracker recent_activity_time < '2013-12-31' allow filtering; 

i understand cannot use other '=' secondary indexed columns.

however, cannot add 'recent_activity_time' key need update column recent activity time of event if any.

any ideas in solving problem highly appreciated.

i can see issue query. you're not hitting partition. such, performance of query quite bad. it'll need query across whole cluster (assuming took measures make work).

if you're looking query last activity time id, think storing in more query friendly format. might try this:

create table tracker (dummy int, day timestamp, id uuid, primary key(dummy, day, id)); 

you can insert day epoch date (ignoring time), , dummy = 0.

that should enable do:

select * tracker dummy=0 , day > '2013-12-31'; 

you can set ttl on insert old entries expire (maybe after year in case). idea you're storing information in way suits query.