Latest Post

Monday, 19 December 2016

What is Secondary Indexes in Cassandra Database

What is Secondary Indexes? How to Create Index? Limitations of secondary indexes? How to Drop Index?

CQL supports creating secondary indexes on tables that allowing queries on the table to use those indexes. A secondary index is identified by a name. Index name is optional. Using CQL, you can create an index on a column after defining a table. Secondary indexes are tricky to use and can impact performance greatly.

CREATE INDEX
Using the CREATE INDEX statement we can create a secondary index on a table.

Syntax: - CREATE INDEX [ index_name ] ON table_name (column_name)

Remark Point: -

  • An index name is optional and must be unique within a keyspace.
  • If you do not provide a name, Cassandra will assign a name like column_name_idx.

Example:-

cqlsh:payroll> CREATE INDEX erank ON exam_year_name (rank);
cqlsh:payroll> select * from exam_year_name where rank=1;

 exam_year | exam_name                 | rank | examlist_name
-----------+---------------------------+------+---------------
      2015 | Public Service Commission |    1 |         UPPSC
      2015 |  Teacher Eligibility Test |    1 |          CTET

(2 rows)
cqlsh:payroll> CREATE INDEX userIndex ON NerdMovies (user);
cqlsh:payroll> CREATE INDEX ON Mutants (abilityId);
cqlsh:payroll> CREATE INDEX ON users (keys(favs));

Limitations of secondary indexes

  1. Do not use an index on high-cardinality values (timestamps, birthdates, keywords etc.).
  2. Do not use an index on tables that use a counter column.
  3. Do not use an index on a frequently updated or deleted column.
  4. Do not use an index on unsorted result values.
  5. Do not use an index you look for a row in a large partition unless narrowly queried.

DROP INDEX
We can drop a secondary index by using the DROP INDEX statement.

Syntax:-  DROP INDEX index_name

Example:-

cqlsh:payroll> CREATE INDEX erank ON exam_year_name (rank);
cqlsh:payroll> select * from exam_year_name where rank=1;

 exam_year | exam_name                 | rank | examlist_name
-----------+---------------------------+------+---------------
      2015 | Public Service Commission |    1 |         UPPSC
      2015 |  Teacher Eligibility Test |    1 |          CTET

(2 rows)
cqlsh:payroll> Drop index erank;
cqlsh:payroll>

Keep in Mind:-
  • The DROP INDEX statement is used to drop an existing secondary index
  • If the index does not exist, the statement will return an error. to avoid this use IF EXISTS



Government Jobs