0

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 ?

Ashraful Islam
  • 12,470
  • 3
  • 32
  • 53
Schmidt
  • 51
  • 7

1 Answers1

0

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)
)
Ashraful Islam
  • 12,470
  • 3
  • 32
  • 53
  • Thanks for you reply, but I saw this **There is an important remark about SPARSE mode. By sparse, it means that for each indexed value, there are very few (maximum 5 actually) matching rows. If there are more than 5 matching rows, an exception will be thrown** Is that ok with my case because it might return more than 5 rows ? – Schmidt Nov 21 '16 at 08:36
  • It seems to me that the time is a high cardinality column > High-cardinality refers to columns with values that are very uncommon or unique. High-cardinality column values are typically identification numbers, email addresses, or user names. An example of a data table column with high-cardinality would be a USERS table with a column named USER_ID. If it is high-cardinality column then you should use sparse – Ashraful Islam Nov 21 '16 at 08:47