What are secondary indexes in Cassandra, and when should you avoid them?

A secondary index lets you query on a non-partition-key column without specifying the partition key, something Cassandra normally forbids for efficiency reasons.

CREATE INDEX ON orders (status);
SELECT * FROM orders WHERE status = 'pending';

Under the hood, each node only indexes the data it locally stores — there is no global index. So a query like the one above has to fan out to every node in the cluster (or every replica for the range) to collect all matching rows, which is fundamentally different from an index in a relational database.

• Avoid secondary indexes on high-cardinality columns (like unique IDs or timestamps) — nearly every row is a separate index entry, giving poor selectivity per index lookup.
• Avoid them on very low-cardinality columns (like a boolean flag) — a huge fraction of rows match, still forcing a broad, expensive scan.
• Avoid them on frequently updated or deleted columns, since index entries generate their own tombstones and can bloat quickly.

Secondary indexes work best for medium-cardinality columns queried occasionally, in small-to-medium clusters. For anything at real scale or on the hot query path, a purpose-built query table (denormalization) is almost always the better data-modeling choice.