Effective CQL - Mastering Apache Cassandra - page 120

• Extremely low cardinality might not add any value : Extremely low cardinality
like a Boolean field (cardinality: 2), or the number of seasons (cardinality: 4) may
not be a good choice as it does not effectively reduce the number of rows to be
pulled drastically. If you have a Boolean column and create an index on it, you
will end up in two gigantic rows in your index table, one for true and another for
false. If the data is evenly distributed for this column, each row in the index table
will hold 50 percent of the rows. For a 40 million record table, you might end up
pulling 20 million records, which might bog down the servers.
• Extremely frequently updated or deleted columns are a bad choice : Cas-
sandra queries fail when tombstone counts exceed the configured value (default:
100,000 cells). This means that if you have a very fast changing column, it may
not be the best idea to use it as an index column. This may change in future. For
more information, visit https://issues.apache.org/jira/browse/CASSANDRA-6117 .
After understanding the introduction , let's now see the index creation syntax:
CREATE [ CUSTOM ] INDEX [ IF NOT EXISTS ] [ index_name]
ON table_name ( { KEYS ( column_name ) | column_name } )
[ USING class_name ] [ WITH OPTIONS = json_object ];
As usual, IF NOT EXISTS is to avoid an error in case it already exists. Most com-
monly, the version is used as follows:
CREATE INDEX IF NOT EXISTS my_simple_index
ON my_table(my_column);
Note that from Cassandra 2.1 onward, you are able to create an index on collections as
well. In case of map, by default, the index is created on map values. To create an index on
map keys, you will have to use the KEYS keyword. Here are some examples:
CREATE TABLE index_demo (id int PRIMARY KEY, name text, age
int, address map<text, text>, phones set <text>);
CREATE INDEX IF NOT EXISTS index_demo_name_idx ON
index_demo (name);
CREATE INDEX index_demo_age_idx ON index_demo (age);
CREATE INDEX IF NOT EXISTS index_demo_phones_idx ON
index_demo (phones);
CREATE INDEX IF NOT EXISTS index_demo_address_key_idx ON
index_demo (KEYS(address));