Search Restrictions Solution: Secondary Index

In Apache Cassandra, the primary key of a table restricts search conditions affecting the design of the relational model. This lesson will address these limitations and explore the secondary index as a solution.

To reiterate, in a SELECT query:

• Only the columns that are part of the primary key can be used in the WHERE clause as filtering conditions.

• Only equality searches can be performed on partition key columns. Range queries are allowed on clustering columns.

• If the partition key is composed of multiple columns (composite partition key), all partition key columns must be included in the WHERE clause. 

• Clustering columns may not be included in the search condition. To include a clustering column in the WHERE clause, all clustering columns defined before it in the primary key must also be included in the search condition.

Secondary index (2i)

A secondary index, denoted by the abbreviation 2i, may be created to query a table based on a non-primary key column, a clustering column, or part of a composite partition key. A secondary index is created on a column of an existing table.

When an index is created, Cassandra creates a table in the background to store the index data. This table has the index column as the partition key and the primary key column(s) of the base table ...