[SOLVED] Cassandra timeuuid comparison

Cassandra timeuuid comparison

I have a table,

CREATE TABLE log (
    uuid uuid,
    time timeuuid,
    user text,
    ....
    PRIMARY KEY (uuid, time, user)
)

and

CREATE CUSTOM INDEX time_idx on Log(time) USING 'org.apache.cassandra.index.sasi.SASIIndex';

then I want to select base on time

select * from Log where time > 84bfd880-b001-11e6-918c-24eda6ab1677;

and nothing return, it will return if I use equal(=). Which step did I go wrong ?

Solution

You need to make time_idx index as SPARSE index.

The SPARSE index is meant to improve performance of querying large, dense number ranges like timestamps for data inserted every millisecond. If the data is numeric, millions of columns values with a small number of partition keys characterize the data, and range queries will be performed against the index, then SPARSE is the best choice. For numeric data that does not meet this criteria, PREFIX is the best choice.

drop the time_idx and create with the below query

CREATE CUSTOM INDEX time_idx on Log(time) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = { 'mode': 'SPARSE' };

Now you can query with The inequalities >=, > and <= .

Limitation : SPARSE indexing is used only for numeric data, so LIKE queries do not apply.

and Another thing your table creation is not right. It should be

CREATE TABLE log (
    uuid uuid,
    time timeuuid,
    user text,
    PRIMARY KEY (uuid, time, user)
)