Cassandra Secondary Indexes

A secondary index lets you query a Cassandra table by a column that is not part of the primary key. Without an index, Cassandra can only filter efficiently on partition key and clustering columns. A secondary index on a non-key column enables WHERE clause filtering on that column without rewriting the table schema.

The Library Card Catalog Analogy

A library shelves books by call number — that is the primary key. If you want to find all books by a specific author, you need a separate author card catalog that maps author names to shelf locations. A Cassandra secondary index works exactly like that author catalog: it keeps a separate local index on each node that maps the indexed column's values to partition keys.

Primary lookup (fast):
  Partition key → row directly

Secondary index lookup (slower):
  Indexed column value
      → local index on each node
          → partition keys
              → full rows fetched

Creating a Secondary Index

-- Table:
CREATE TABLE customers (
  customer_id UUID PRIMARY KEY,
  first_name  TEXT,
  last_name   TEXT,
  email       TEXT,
  city        TEXT
);

-- Index on the city column:
CREATE INDEX idx_customers_city ON customers (city);

Index with Custom Name

CREATE INDEX idx_customers_email ON customers (email);

Index Without a Name (auto-generated name)

CREATE INDEX ON customers (city);

Querying with a Secondary Index

-- Before index: this requires ALLOW FILTERING
SELECT * FROM customers WHERE city = 'London';

-- After index: works without ALLOW FILTERING
SELECT * FROM customers WHERE city = 'London';

How the Secondary Index Works Internally

Cassandra builds a hidden local index table on each node. The index stores the indexed column's value as the partition key and the base table's partition key as the clustered value. When you query by the indexed column, the coordinator asks every node to look up the value in its local index. Each node finds the matching partition keys and fetches the full rows. The coordinator aggregates results from all nodes.

Cluster: 3 nodes (A, B, C) with customers spread across all

Query: WHERE city = 'Berlin'

Step 1: Coordinator sends query to Node A, B, and C
Step 2: Each node checks its LOCAL index for city='Berlin'
  Node A: finds customer_id-1, customer_id-4
  Node B: finds customer_id-7
  Node C: finds no matches
Step 3: Nodes fetch full rows by partition key
Step 4: Coordinator aggregates → returns 3 rows

When Secondary Indexes Work Well

Good Fit                              Poor Fit
──────────────────────────────────────────────────────────────
Low-cardinality columns with          High-cardinality unique
  moderate result counts              columns (like email)
  (e.g., country, status, gender)
Tables with many partitions           Very small tables
  where rows distribute evenly        (scan is fast without index)
Columns queried alongside the         Columns queried without
  partition key                       partition key on large tables

Low Cardinality Example (Good)

-- status has 3 values: 'active', 'inactive', 'pending'
CREATE INDEX ON subscriptions (status);

SELECT * FROM subscriptions
WHERE customer_id = [uuid]  -- partition key
  AND status = 'active';    -- secondary index

High Cardinality Example (Avoid)

-- email is unique per customer — millions of distinct values
-- This causes every node to check its local index every time
CREATE INDEX ON customers (email);  -- poor choice

-- Better: create a separate lookup table
CREATE TABLE customers_by_email (
  email       TEXT PRIMARY KEY,
  customer_id UUID
);

Indexes on Collection Columns

Cassandra supports secondary indexes on the values inside a collection, or on the keys of a MAP column.

-- Index on SET or LIST values:
CREATE INDEX ON articles (tags);
SELECT * FROM articles WHERE tags CONTAINS 'cassandra';

-- Index on MAP values:
CREATE INDEX ON user_prefs (settings);
SELECT * FROM user_prefs WHERE settings CONTAINS 'dark';

-- Index on MAP keys:
CREATE INDEX ON user_prefs (KEYS(settings));
SELECT * FROM user_prefs WHERE settings CONTAINS KEY 'theme';

Dropping an Index

DROP INDEX idx_customers_city;
DROP INDEX IF EXISTS idx_customers_city;

Listing Indexes on a Table

DESCRIBE TABLE customers;
-- The output includes CREATE INDEX statements for all indexes on the table.

Limitations of Secondary Indexes

Limitation                          Implication
──────────────────────────────────────────────────────────────────
Local index — not global            Every query fans out to all nodes
High write overhead                 Every write updates the index on
                                    the node holding the partition
No range queries                    WHERE city > 'L' is not supported
                                    (use SASI for range/like queries)
No use across partitions            Designed to complement partition
  without partition key             key filtering; avoid using alone
Not good for high-cardinality data  Leads to large index partitions
                                    and slow lookups

When to Use a Lookup Table Instead

For columns you query frequently and that have high cardinality (unique or near-unique values), create a dedicated lookup table instead of a secondary index. The lookup table is just a regular Cassandra table where the frequently queried column becomes the partition key.

-- Lookup table for email:
CREATE TABLE customers_by_email (
  email       TEXT PRIMARY KEY,
  customer_id UUID
);

-- Application inserts into both tables on customer creation.
-- Query: fast partition key lookup
SELECT customer_id FROM customers_by_email WHERE email = 'alice@example.com';

Summary

Secondary indexes let you filter on non-primary-key columns without restructuring your table. They work best on low-cardinality columns where the result set is manageable in size. They perform poorly on high-cardinality columns or when used without a partition key filter on large tables. For high-cardinality filtering, use a dedicated lookup table. For text search and range queries on non-key columns, use SASI indexes (covered in the next topic).