How to optimally design data model to select by date, which couldn't be PK?

ScyllaDB
1

My table is

CREATE TABLE cache_entries(
	created timestamp, 
	expired timestamp, 
	request_key text, 
	response_key text,
	method text,
	uri text,
	response_code int,
	PRIMARY KEY (request_key, created))
WITH CLUSTERING ORDER BY (created DESC);

I find expired data by query SELECT ... WHERE expired < ? ALLOW FILTERING and then remove files, which are linked with found records, and drop records by PK. So I cannot use TTL feature. Is there a way to avoid using of ALLOW FILTERING in my case?

2

Whenever you find yourself having to use ALLOW FILTERING, a possible alternative is to create a secondary index on the filtered columns. This is a trade-off: creating a secondary index will speed up the queries but it is extra load on the cluster, as the secondary index has to be kept in sync with the base table and thus be updated on each write.
If you do this filtering read often, using an index might be a good choice. If this filtering read is rare, then you can just keep using filtering, nothing wrong with that.

1 Like