Hi
I have the following table:
CREATE TABLE users (
username text,
last_seen bigint,
PRIMARY KEY (username)
);
where* last_seen* is basically the writetime . Number of records in the
table is aprox 10 million. Insert is pretty much straightforward insert
into users (username, last_seen) VALUES ([username], now)
I want to make some processing on users that were not seen for the past XXX
(where xxx can be hours/days ... ) by query the last_seen column (this
query runs every minute) e.g :
select username from users where last_seen < (now - 1 day).
I have two options as I see it:
1. use materialized view :
CREATE MATERIALIZED VIEW users_last_seen AS
SELECT last_seen, username
FROM users
WHERE last_seen IS NOT NULL
PRIMARY KEY (last_seen, username);
and simply query:
select username from users_last_seen where last_seen < (now - 1 day)
1.
query the users table
select username from users where last_seen < (now - 1 day) ALLOW
FILTERING
which one is more efficient? any other options ?
Any help will be greatly appreciated
Best
Avi
