Search⌘ K
AI Features
Home
Courses
Introduction to Apache Cassandra

Search Restrictions Solution: Secondary Index

Explore how secondary indexes in Apache Cassandra enable querying on non-primary key columns by creating index tables. Understand when to use secondary indexes, their limitations, anti-patterns, and how to create or drop them using CQL commands. This lesson helps you manage flexible queries while balancing performance impacts.

We'll cover the following...

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 ...