Cassandra prefix search

2
votes

I have table CREATE TABLE site(url text PRIMARY KEY, count int) with data

com.google 5

com.google.subdomain 10

Does Cassandra support select all google sites (with subdomain) in single query?

1
cassandra

1 Answers

2
votes

Cassandra has SASI index type that allows effective indexing of the text for prefix search & contains. But it couldn't be used to index the partition key like in your case. One potential workaround could be to put the copy of the same data (or only domain part) into the table as ordinary column, and index that column. (This blog post has very detailed description of internals of SASI indices).

If you'll do this operation very often, then maybe you'll need to re-model data, and for example, use table of following structure:

create table site(
   ps text,
   url text,
   count int,
   primary key (ps, url));

where ps is public suffix of domain. But this will depend on do you count the individual URLs, or only domains, otherwise you'll get too wide rows for sites like google, facebook, etc.