Secondary index in cassandra

 

cqlsh:aug_space> select * from movies;

 movie_id                             | age_rating | genres                | release_date                    | title

--------------------------------------+------------+-----------------------+---------------------------------+-----------------

 236d3970-97f1-4f24-9bb1-d657cc698dac |          8 | {'animation', 'kids'} | 2023-09-09 00:00:00.000000+0000 | Jurassic Park 2

 41d8ffdf-fb1e-43d0-85ae-9c4f358f35c2 |          9 | {'animation', 'kids'} | 2023-09-09 00:00:00.000000+0000 | Sturat Little 2

 6ae5a279-36e0-49db-b39d-f9b582f082a3 |          8 | {'animation', 'kids'} | 2023-09-09 00:00:00.000000+0000 |       Avatar  2

 d9573b24-02cb-49b4-91ba-c040c5a0127a |          7 | {'animation', 'kids'} | 2023-09-09 00:00:00.000000+0000 |          Avatar

 93762f96-005c-4bd9-8994-1a6454a91334 |          9 | {'animation', 'kids'} | 2023-09-09 00:00:00.000000+0000 |   Jurassic Park

 7b085f61-4202-4bd8-b84c-f2a46baf0853 |          9 | {'animation', 'kids'} | 2023-09-09 00:00:00.000000+0000 |   Sturat Little

cqlsh:aug_space> select * from movies where title='avatar';

InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"

cqlsh:aug_space> select  token(movie_id) , movie_id from movies;

 system.token(movie_id) | movie_id

------------------------+--------------------------------------

   -7679664659544709434 | 236d3970-97f1-4f24-9bb1-d657cc698dac

   -5854020856241272382 | 41d8ffdf-fb1e-43d0-85ae-9c4f358f35c2

   -5353160429242411980 | 6ae5a279-36e0-49db-b39d-f9b582f082a3

   -5014329844387628670 | d9573b24-02cb-49b4-91ba-c040c5a0127a

    3478065203213068432 | 93762f96-005c-4bd9-8994-1a6454a91334

    6637426536803809222 | 7b085f61-4202-4bd8-b84c-f2a46baf0853

(6 rows)

cqlsh:aug_space> CREATE INDEX IF NOT EXISTS ON movies (title);

cqlsh:aug_space> select * from movies where title='Avatar';

 movie_id                             | age_rating | genres                | release_date                    | title

--------------------------------------+------------+-----------------------+---------------------------------+--------

 d9573b24-02cb-49b4-91ba-c040c5a0127a |          7 | {'animation', 'kids'} | 2023-09-09 00:00:00.000000+0000 | Avatar

cqlsh:aug_space> select * from movies_by_ratings where age_rating=9;

 age_rating | movie_id                             | genres                | release_date                    | title

------------+--------------------------------------+-----------------------+---------------------------------+-----------------

          9 | 41d8ffdf-fb1e-43d0-85ae-9c4f358f35c2 | {'animation', 'kids'} | 2023-09-09 00:00:00.000000+0000 | Sturat Little 2

          9 | 7b085f61-4202-4bd8-b84c-f2a46baf0853 | {'animation', 'kids'} | 2023-09-09 00:00:00.000000+0000 |   Sturat Little

          9 | 93762f96-005c-4bd9-8994-1a6454a91334 | {'animation', 'kids'} | 2023-09-09 00:00:00.000000+0000 |   Jurassic Park

(3 rows)

cqlsh:aug_space> desc table movies;

CREATE TABLE aug_space.movies (

    movie_id uuid PRIMARY KEY,

    age_rating int,

    genres set<text>,

    release_date timestamp,

    title text

) WITH bloom_filter_fp_chance = 0.01

    AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}

    AND comment = ''

    AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold': '32', 'min_threshold': '4'}

    AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}

    AND crc_check_chance = 1.0

    AND dclocal_read_repair_chance = 0.1

    AND default_time_to_live = 0

    AND gc_grace_seconds = 864000

    AND max_index_interval = 2048

    AND memtable_flush_period_in_ms = 0

    AND min_index_interval = 128

    AND read_repair_chance = 0.0

    AND speculative_retry = '99PERCENTILE';

CREATE INDEX movies_title_idx ON aug_space.movies (title);

CREATE INDEX movies_age_rating_idx ON aug_space.movies (age_rating);

for more information -->

SASI & regular index ---

https://www.baeldung.com/cassandra-secondary-indexes 

Use Where Clause in Cassandra - CQL

 

How to use Where Clause in Cassandra (CQL)

Where clause in Cassandra Query Language is used to filter the records.  To filter the records, you will be using 

• Partition Key ( Single or composite)
• A cluster column with Partition Key
• A column on which you have created the index
• A column which is not part of Partition Key or no index is created on it, you can use in where clause but you have to use with Allow Filtering.