Use index in Cassandra on a table having many row than contain te indexed value(low or normal cardinality). the more unique value that exist in a particular column, the more overhead you wil have, on average to query and maintenance the index. For example, suppose you hand a playlists table with a billion songs and wanted to lookup songs by the genre, Many songs will share the same column value for genre.
The genre column is a good candidate for an index. When we no to user an index on column have a High-cardinality, in the table than use a counter column, On a Frequently update or deleted column and to look for a row in a large partition unless narrowly queried. datastack
Sysntax
|
1 2 3 4 |
CREATE CUSTOM INDEX IF NOT EXISTS index_name ON keyspace_name.table_name ( KEYS ( column_name ) ) USING class_name WITH OPTIONS = map |
Example Creating an index on a column
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE putracode.customer_with_index( id uuid, first_name text, last_name text, email text, zip_code int, city text, PRIMARY KEY (id) ); CREATE INDEX customer_zip_code ON putracode.customer_with_index(zip_code); CREATE INDEX ON putracode.customer_with_index(city); //without index name |
when we create index without specify index name, cassandra will automaticly creating index name TABLENAME_COLUMNNAME_idx.
Creating an index on a clustering column
define table with composite column
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE putracode.customer_with_index2( id uuid, first_name text, last_name text, email text, zip_code int, city text, PRIMARY KEY ((id,lastname), city) ); CREATE INDEX ON putracode.customer_with_index2(city); |
Creating an index on a collection
create an index on a collection column as we would any other column. Enclose the name of the collection column in parentheses at the end of the CREATE INDEX statement. For example, add a collection of phone numbers to the customer_with_index table to index the data in the phones set.
|
1 2 3 |
ALTER TABLE customer_with_index ADD phones set<text>; CREATE INDEX ON customer_with_index (phones); |
Creating an index on map keys
We can create an index on map collection keys. If an index of the map values of the collection exists, drop that index before creating an index on the map collection keys.To index map keys, you use the KEYS keyword and map name in nested parentheses.
For example, index the collection keys, the timestamps, in the todo map in the users table:
|
1 2 3 |
ALTER TABLE customer_with_index ADD todo_dates map<timestamp,text>; CREATE INDEX todo_dates_index ON customer_with_index (KEYS(todo_dates)); |
To query the table, we can use CONTAINS KEY in WHERE clauses.
Drop index
|
1 2 |
DROP INDEX IF EXISTS keyspace.index_name |
