Advanced CQL Features

Indexes in Cassandra

Indexes in Cassandra allow you to quickly search data using non-primary-key columns. They act like lookup shortcuts — helping queries run faster when filtering on specific fields.

📖 The Story: Lisa's Slow Query Crisis

Lisa built an e-commerce app with 10 million products. Users want to search by brand, category, and price. She modeled the table with product_id as the partition key...

❌ Attempt 1: Query Without Index (DISASTER!)

CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    name TEXT,
    brand TEXT,
    category TEXT,
    price DECIMAL
);

-- User searches for Nike products
SELECT * FROM products
WHERE brand = 'Nike';

-- ERROR: Cannot execute this query as it might involve data filtering
                

Problems:

💥 Query fails! Can't filter by non-primary key columns
🐌 Even with ALLOW FILTERING, scans ALL 10 million rows!
⏱️ Query takes 45 seconds (timeout!)
😡 Users abandon the site - sales plummet!

⚠️ Attempt 2: ALLOW FILTERING (Hacky & Slow!)

SELECT * FROM products
WHERE brand = 'Nike'
ALLOW FILTERING;  -- Works but SCANS ENTIRE TABLE!
                

Problems:

🔥 Full table scan on 10 million rows!
⏱️ Takes 30+ seconds per query
💸 Wastes CPU, memory, and disk I/O
📉 Can't scale - site crashes under load

✅ The RIGHT Way: Secondary Index!

-- Step 1: Create index on brand column
CREATE INDEX idx_brand ON products(brand);

-- Step 2: Query now works FAST!
SELECT * FROM products
WHERE brand = 'Nike';

-- ⚡ Returns in 50ms!
-- ✅ Only scans Nike products (500K out of 10M)
                

Benefits:

✅ Fast queries: 50ms vs 30 seconds!
✅ No full table scan: Only reads relevant partitions
✅ Users are happy: Instant search results!
✅ Scalable: Works even with 100M products
✅ Easy to create: One command, done!

Lisa's site is now blazing fast! Sales are up 300%! 🎉

📑 What are Indexes in Cassandra?

Indexes allow you to query by non-primary key columns efficiently without full table scans.

Simple Definition

Secondary Index: A data structure that allows fast lookups on non-primary key columns.

Think of an Index as:

📚 Book Index: Jump to specific pages without reading everything
🗂️ Filing System: Find documents by category, not just ID
🔍 Search Engine: Quickly locate items by any attribute
🗺️ Map Legend: Find locations by name, not coordinates

Why Do We Need Indexes?

❌

Without Index

Can't Query: WHERE on non-PK columns fails
ALLOW FILTERING: Scans entire table (slow!)
Full Table Scan: Reads ALL rows every query
Poor Performance: Seconds or minutes per query
Not Scalable: Gets worse as data grows

✅

With Index

Fast Queries: Milliseconds instead of seconds
Targeted Reads: Only scans matching rows
No Filtering: Index does the work for you
Flexible Queries: Search by any indexed column
Better UX: Users get instant results

Types of Indexes in Cassandra

1️⃣ Secondary Index (Regular Index)

Most common type - indexes a single column for equality queries.

CREATE INDEX idx_category ON products(category);

2️⃣ SASI (SSTable Attached Secondary Index)

Advanced index supporting range queries, LIKE, and case-insensitive searches.

CREATE CUSTOM INDEX idx_name ON products(name)
USING 'org.apache.cassandra.index.sasi.SASIIndex';
                

3️⃣ Collection Index

Index on collection types (LIST, SET, MAP) to query elements.

CREATE INDEX idx_tags ON products(tags); -- tags is SET<TEXT>

🔨 Creating and Using Indexes

Master the complete index lifecycle: CREATE, USE, DROP.

Creating a Secondary Index

-- Basic Syntax
CREATE INDEX [index_name] ON table_name (column_name);

-- Create index with explicit name
CREATE INDEX idx_brand ON products(brand);

-- Create index without name (Cassandra generates one)
CREATE INDEX ON products(category);

-- Create index on multiple columns (separate indexes!)
CREATE INDEX idx_brand ON products(brand);
CREATE INDEX idx_category ON products(category);
    

Important: One Column Per Index!

Cassandra doesn't support composite indexes! Each index covers only ONE column.

-- ❌ This does NOT work:
CREATE INDEX ON products(brand, category);  -- ERROR!

-- ✅ Create separate indexes instead:
CREATE INDEX ON products(brand);
CREATE INDEX ON products(category);
        

Querying with Indexes

-- Example table
CREATE TABLE products (
    product_id UUID PRIMARY KEY,
    name TEXT,
    brand TEXT,
    category TEXT,
    price DECIMAL,
    stock INT
);

-- Create indexes
CREATE INDEX idx_brand ON products(brand);
CREATE INDEX idx_category ON products(category);

-- ✅ Query using single index
SELECT * FROM products
WHERE brand = 'Nike';

-- ✅ Query using different index
SELECT * FROM products
WHERE category = 'Shoes';

-- ✅ Query using both indexes (Cassandra picks one)
SELECT * FROM products
WHERE brand = 'Nike' AND category = 'Shoes'
ALLOW FILTERING;  -- Still need ALLOW FILTERING for multi-column
    

Dropping an Index

-- Drop index by name
DROP INDEX idx_brand;

-- Drop using keyspace.index_name
DROP INDEX ecommerce.idx_category;

-- Check existing indexes
DESCRIBE TABLE products;  -- Shows all indexes
    

Collection Indexes

-- Table with collections
CREATE TABLE articles (
    article_id UUID PRIMARY KEY,
    title TEXT,
    tags SET<TEXT>,
    categories LIST<TEXT>,
    metadata MAP<TEXT, TEXT>
);

-- Index on SET
CREATE INDEX idx_tags ON articles(tags);

-- Index on LIST
CREATE INDEX idx_categories ON articles(categories);

-- Index on MAP VALUES
CREATE INDEX idx_metadata ON articles(VALUES(metadata));

-- Index on MAP KEYS
CREATE INDEX idx_metadata_keys ON articles(KEYS(metadata));

-- Query using collection index
SELECT * FROM articles
WHERE tags CONTAINS 'cassandra';  -- Uses idx_tags

SELECT * FROM articles
WHERE metadata['author'] = 'John';  -- Uses idx_metadata
    

⚙️ How Indexes Work Internally

Understanding the internals helps you use indexes wisely!

Index Internals

Index Table: Cassandra creates a hidden table: indexed_column → partition_key
Local to Each Node: Each node maintains indexes for its own data
No Global Index: Query coordinator must contact ALL nodes!
Automatic Updates: Index updates when you INSERT/UPDATE/DELETE
Write Overhead: Every write updates both table AND index

🎯 When to Use (and NOT Use) Indexes

Indexes aren't always the answer! Choose wisely.

✅

Good Use Cases

High Cardinality: Many unique values (email, username)
Selective Queries: Filter returns small % of rows
Read-Heavy: More reads than writes
Equality Searches: WHERE column = value
Collection CONTAINS: Search within SET/LIST/MAP

❌

Bad Use Cases

Low Cardinality: Few unique values (gender: M/F)
Non-Selective: Returns most rows (active: true)
Write-Heavy: High INSERT/UPDATE volume
Range Queries: WHERE price > 100 (use SASI)
Counter Columns: Can't index counters!

💡

Better Alternatives

Denormalize: Create users_by_email table
Materialized Views: Auto-maintained tables
Application-Level: Cache or external search (Elasticsearch)
Better Modeling: Make column part of primary key
SASI Index: For range queries and LIKE

Cardinality Examples

✅ HIGH Cardinality (Good for Indexes)

email: millions of unique values
username: every user is different
product_sku: each product unique
transaction_id: every transaction different

❌ LOW Cardinality (Bad for Indexes)

gender: only 2-3 values (M/F/Other)
active: only 2 values (true/false)
status: 4-5 values (pending/approved/rejected)
country: ~200 values (might work for small tables)

🚀 SASI Indexes: Advanced Indexing

SASI (SSTable Attached Secondary Index) supports range queries, LIKE searches, and more!

What is SASI?

SASI is an advanced indexing mechanism that overcomes limitations of regular secondary indexes.

SASI Advantages:

✅ Range Queries: WHERE price > 100 AND price < 500
✅ LIKE Searches: WHERE name LIKE '%phone%'
✅ Case-Insensitive: Search regardless of case
✅ Prefix/Suffix: name LIKE 'iphone%' or '%.com'
✅ Better Performance: Lower memory footprint

Creating SASI Indexes

-- Basic SASI Index (for equality)
CREATE CUSTOM INDEX sasi_brand ON products(brand)
USING 'org.apache.cassandra.index.sasi.SASIIndex';

-- SASI Index with PREFIX mode (for LIKE 'abc%')
CREATE CUSTOM INDEX sasi_name ON products(name)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
    'mode': 'PREFIX',
    'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
    'case_sensitive': 'false'
};

-- SASI Index with CONTAINS mode (for LIKE '%abc%')
CREATE CUSTOM INDEX sasi_description ON products(description)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
    'mode': 'CONTAINS',
    'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
    'case_sensitive': 'false'
};

-- SASI Index for numeric range queries
CREATE CUSTOM INDEX sasi_price ON products(price)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {'mode': 'SPARSE'};
    

SASI Query Examples

-- Range queries (NUMERIC)
SELECT * FROM products
WHERE price >= 100 AND price <= 500;

-- Prefix search
SELECT * FROM products
WHERE name LIKE 'iPhone%';

-- Contains search
SELECT * FROM products
WHERE description LIKE '%wireless%';

-- Suffix search
SELECT * FROM users
WHERE email LIKE '%@gmail.com';

-- Case-insensitive search
SELECT * FROM products
WHERE name LIKE '%IPHONE%';  -- Works with case_sensitive: false

-- Combine with partition key for best performance
SELECT * FROM products
WHERE category = 'Electronics'  -- Partition key
  AND price >= 100 AND price <= 500;
    

SASI Modes

PREFIX: LIKE 'abc%' - starts with
CONTAINS: LIKE '%abc%' - anywhere
SPARSE: Numeric ranges, high cardinality

SASI Analyzers

NonTokenizingAnalyzer: Exact matches, IDs
StandardAnalyzer: Text search, tokenization
DelimiterAnalyzer: Custom delimiters

🚀 SASI Indexes: Advanced Indexing

SASI (SSTable Attached Secondary Index) supports range queries, LIKE searches, and more!

What is SASI?

SASI is an advanced indexing mechanism that overcomes limitations of regular secondary indexes.

SASI Advantages:

✅ Range Queries: WHERE price > 100 AND price < 500
✅ LIKE Searches: WHERE name LIKE '%phone%'
✅ Case-Insensitive: Search regardless of case
✅ Prefix/Suffix: name LIKE 'iphone%' or '%.com'
✅ Better Performance: Lower memory footprint

Creating SASI Indexes

-- Basic SASI Index (for equality)
CREATE CUSTOM INDEX sasi_brand ON products(brand)
USING 'org.apache.cassandra.index.sasi.SASIIndex';

-- SASI Index with PREFIX mode (for LIKE 'abc%')
CREATE CUSTOM INDEX sasi_name ON products(name)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
    'mode': 'PREFIX',
    'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer',
    'case_sensitive': 'false'
};

-- SASI Index with CONTAINS mode (for LIKE '%abc%')
CREATE CUSTOM INDEX sasi_description ON products(description)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
    'mode': 'CONTAINS',
    'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
    'case_sensitive': 'false'
};

-- SASI Index for numeric range queries
CREATE CUSTOM INDEX sasi_price ON products(price)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {'mode': 'SPARSE'};
    

SASI Query Examples

-- Range queries (NUMERIC)
SELECT * FROM products
WHERE price >= 100 AND price <= 500;

-- Prefix search
SELECT * FROM products
WHERE name LIKE 'iPhone%';

-- Contains search
SELECT * FROM products
WHERE description LIKE '%wireless%';

-- Suffix search
SELECT * FROM users
WHERE email LIKE '%@gmail.com';

-- Case-insensitive search
SELECT * FROM products
WHERE name LIKE '%IPHONE%';  -- Works with case_sensitive: false

-- Combine with partition key for best performance
SELECT * FROM products
WHERE category = 'Electronics'  -- Partition key
  AND price >= 100 AND price <= 500;
    

SASI Modes

PREFIX: LIKE 'abc%' - starts with
CONTAINS: LIKE '%abc%' - anywhere
SPARSE: Numeric ranges, high cardinality

SASI Analyzers

NonTokenizingAnalyzer: Exact matches, IDs
StandardAnalyzer: Text search, tokenization
DelimiterAnalyzer: Custom delimiters

🖥️ Interactive Index Console

Practice index commands in our safe simulator!

CQL Index Playground

Enter CQL Command:

🚀 Index Simulator Ready!
Try the examples or create your own index...

Available Examples:
• Example 1: Create regular index
• Example 2: SASI index with CONTAINS
• Example 3: Collection index

⭐ Index Best Practices & Common Mistakes

Production-proven strategies and pitfalls to avoid!

✅

DO's

High Cardinality: Index columns with many unique values
Name Indexes: Use descriptive names (idx_email)
Combine with PK: WHERE pk = X AND indexed_col = Y
Monitor Performance: Track index usage and query times
Use SASI for Ranges: Better than regular index
Test Before Production: Measure impact on writes
Limit Indexes: 3-5 per table maximum

❌

DON'Ts

Don't Index Low Cardinality: gender, boolean, status
Don't Over-Index: Every index slows writes!
Don't Index Write-Heavy: High INSERT/UPDATE tables
Don't Use for Ranges: Regular index (use SASI instead)
Don't Forget ALLOW FILTERING: Multi-column queries need it
Don't Index Large Text: Use external search (Elasticsearch)
Don't Index Counters: Not supported!

💡

Pro Tips

Check Query Plans: TRACING ON to see index usage
Rebuild Indexes: nodetool rebuild_index after major writes
Denormalize Instead: Often faster than indexes
Use Materialized Views: Auto-maintained denormalization
External Search: Elasticsearch for complex text search
Monitor Write Latency: Indexes add 10-30% overhead
Drop Unused Indexes: Free up resources

⚠️ Common Mistake: Indexing Low Cardinality Column

The Problem: Developer creates index on 'gender' column with only 3 values (M/F/Other)...

-- ❌ BAD: Low cardinality column
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    name TEXT,
    gender TEXT  -- Only 3 values: M/F/Other
);

CREATE INDEX idx_gender ON users(gender);  -- ❌ Terrible idea!

SELECT * FROM users WHERE gender = 'M';
-- Returns 50% of all users! (5 million out of 10 million)
-- Coordinator must contact ALL nodes!
-- Index provides NO benefit - still scans millions of rows!
            

Why It's Bad:

🔥 Query still returns millions of rows (not selective)
⏱️ Must scan ~50% of table anyway
💸 Index takes up disk space for no benefit
🐌 Slows down every INSERT/UPDATE/DELETE

The Fix:

-- ✅ Option 1: Denormalize with separate table
CREATE TABLE users_by_gender (
    gender TEXT,
    user_id UUID,
    name TEXT,
    PRIMARY KEY (gender, user_id)
);

-- ✅ Option 2: Use Materialized View (auto-maintained)
CREATE MATERIALIZED VIEW users_by_gender AS
    SELECT gender, user_id, name FROM users
    WHERE gender IS NOT NULL AND user_id IS NOT NULL
    PRIMARY KEY (gender, user_id);

-- ✅ Option 3: Filter in application layer
-- Fetch all users and filter by gender in code
            

Index Performance Impact

Write Overhead:

Each index adds 10-30% write latency
3 indexes = 30-90% slower writes!
Index must be updated on every INSERT/UPDATE/DELETE

Storage Overhead:

Index size ≈ 10-20% of table size
Stored on every node (not replicated separately)

Query Performance:

✅ Selective queries (< 10% rows): 100x faster
⚠️ Non-selective (> 30% rows): Marginal benefit
❌ Low cardinality: No benefit, adds overhead

💼 Interview Questions & Expert Answers

Ace your Cassandra interview with these index questions!

1 What is a secondary index in Cassandra and how does it work? ▼

Answer:

A secondary index in Cassandra allows you to query by non-primary key columns without using ALLOW FILTERING.

How It Works Internally:

Hidden Table: Cassandra creates a hidden index table that maps indexed_column → partition_key
Local Indexes: Each node maintains indexes only for its own data (no global index)
Scatter-Gather: Query coordinator contacts ALL nodes to find matching rows
Merge Results: Coordinator merges results from all nodes and returns to client

Example:

CREATE INDEX idx_email ON users(email);

-- Internal structure (simplified):
-- Index Table: email → user_id
-- 'john@email.com' → [uuid-123]
-- 'jane@email.com' → [uuid-456]

SELECT * FROM users WHERE email = 'john@email.com';
-- 1. Looks up 'john@email.com' in index → finds uuid-123
-- 2. Fetches user row with uuid-123
                    

Key Limitation: Because indexes are local to each node, the coordinator must query ALL nodes, which can be slow in large clusters.

2 When should you use an index vs denormalization? ▼

Answer:

The choice depends on your read/write ratio, cardinality, and performance requirements.

Use Secondary Index When:

✅ Read-heavy workload: More reads than writes
✅ High cardinality: Millions of unique values (email, username)
✅ Selective queries: Returns < 10% of rows
✅ Ad-hoc queries: Don't know all query patterns upfront
✅ Simple equality: WHERE column = value

Use Denormalization (Separate Table) When:

✅ Write-heavy workload: High INSERT/UPDATE volume
✅ Low cardinality: Few unique values (< 100)
✅ Predictable queries: Know exact query patterns
✅ Non-selective: Returns > 30% of rows
✅ Best performance: Fastest possible reads

-- Scenario: Query users by country

-- ❌ Index (if write-heavy or low cardinality):
CREATE INDEX idx_country ON users(country);

-- ✅ Denormalization (better for this use case):
CREATE TABLE users_by_country (
    country TEXT,
    user_id UUID,
    name TEXT,
    email TEXT,
    PRIMARY KEY (country, user_id)
);

-- Query is blazing fast (no scatter-gather!):
SELECT * FROM users_by_country
WHERE country = 'USA';
                    

Trade-off: Denormalization requires duplicate data and application-level write coordination, but provides better performance.

3 What is SASI and how is it different from regular indexes? ▼

Answer: SASI (SSTable Attached Secondary Index) is an advanced index type supporting range queries and text search.

Key Differences:

Feature	Regular Index	SASI Index
Equality	✅ Supported	✅ Supported
Range Queries	❌ Not supported	✅ Supported
LIKE Searches	❌ Not supported	✅ Supported
Case-Insensitive	❌ Not supported	✅ Configurable
Memory Usage	Higher	Lower

SASI Examples:

-- SASI for range queries
CREATE CUSTOM INDEX sasi_price ON products(price)
USING 'org.apache.cassandra.index.sasi.SASIIndex';

SELECT * FROM products
WHERE price >= 100 AND price <= 500;  -- ✅ Works!

-- SASI for text search
CREATE CUSTOM INDEX sasi_name ON products(name)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = {
    'mode': 'CONTAINS',
    'case_sensitive': 'false'
};

SELECT * FROM products
WHERE name LIKE '%iPhone%';  -- ✅ Works!
                    

4 Why should you avoid indexing low cardinality columns? ▼

Answer: Low cardinality columns have few unique values, making indexes inefficient and wasteful.

What is Low Cardinality?

Columns with only a few distinct values relative to the total number of rows.

Examples:

❌ gender: Only 2-3 values (M/F/Other)
❌ status: Only 3-5 values (active/inactive/suspended)
❌ boolean flags: Only 2 values (true/false)
❌ country: ~200 values (low for millions of rows)

Why It's Bad:

Non-Selective Queries: Returns huge % of rows (e.g., 50% for gender='M')
Coordinator Overhead: Must contact ALL nodes and merge millions of results
No Performance Gain: Still scans millions of rows anyway
Write Penalty: Index slows writes for zero benefit
Storage Waste: Index takes disk space with no value

-- Bad Example: 10M users, 50% are male
CREATE INDEX idx_gender ON users(gender);  -- ❌ DON'T DO THIS!

SELECT * FROM users WHERE gender = 'M';
-- Returns 5 MILLION rows!
-- Coordinator must:
--   1. Query all nodes (network overhead)
--   2. Scan millions of rows per node
--   3. Merge 5M results (memory overhead)
-- Index provides ZERO benefit!
                    

Better Alternatives:

✅ Denormalize: Create users_by_gender table
✅ Materialized View: Auto-maintained denormalization
✅ Application Filter: Fetch all, filter in code (small datasets)
✅ Composite Key: Make it part of primary key if possible

Rule of Thumb: Only index if column has > 1000 unique values AND queries return < 10% of rows.

5 What happens internally when you create an index on a large existing table? ▼

Answer: Cassandra performs a background index build process across all nodes.

Index Creation Process:

Schema Change: Index definition is added to schema and replicated via gossip
Background Build: Each node independently scans its local SSTables
Index Population: For each row, extract indexed_column → partition_key mapping
Index Storage: Write index entries to hidden index table
Compaction: Index SSTables are compacted like regular tables

What You'll See:

-- Create index on large table (10M rows)
CREATE INDEX idx_email ON users(email);

-- Returns immediately! But index is building in background

-- Check build status
nodetool compactionstats
-- Shows: "Index build for idx_email: 3.5M/10M (35%)"

-- Monitor logs
-- INFO: Building secondary index idx_email for users
-- INFO: Index build completed in 45 seconds
                    

Performance Impact During Build:

🔥 CPU Usage: Spikes to 80-90% on all nodes
💾 Disk I/O: Heavy reads from SSTables
⏱️ Time: ~1-2 minutes per 1M rows
📊 Compaction: Pauses other compactions

Can You Query During Build?

✅ Yes, but: Queries may miss rows not yet indexed
⚠️ Partial Results: Only rows indexed so far are returned
✅ After Build: All queries return complete results

Best Practice:

-- 1. Create index during low-traffic window
-- 2. Monitor progress: nodetool compactionstats
-- 3. Wait for completion before relying on queries
-- 4. Rebuild if needed: nodetool rebuild_index keyspace table idx_name
                    

🎓 Chapter Summary: Index Mastery

Congratulations! You now understand Cassandra Indexes at a production level!

Key Concepts Mastered:

Secondary Indexes: Allow queries on non-primary key columns
Local Indexes: Each node maintains indexes for its own data
SASI Indexes: Support range queries, LIKE, and case-insensitive search
Cardinality Matters: Only index high-cardinality columns
Write Trade-off: Indexes add 10-30% overhead to writes

The Golden Rules:

1. High Cardinality Only
Don't index gender, status, or boolean fields!

2. Selective Queries
Indexes work best when returning < 10% of rows

3. Consider Denormalization
Often faster and more scalable than indexes

When to Use What:

🎯 Regular Index: High cardinality, equality queries (email, username)
🚀 SASI Index: Range queries, text search (price, name)
📦 Collection Index: CONTAINS queries on SET/LIST/MAP
🏗️ Denormalization: Low cardinality, write-heavy, best performance
🔍 External Search: Complex text search (use Elasticsearch)

🚀 You're now equipped to build blazing-fast queries with indexes!

Responsive Ad