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.

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.