Day 6: Cassandra Lab Practice – Indexes and Performance

byMy Scribble Pads -


✅ Day 6: Secondary Indexes & Basic Performance Considerations

🎯 Objective:

Learn how to create and use secondary indexes in Cassandra, and understand when and why they should (or shouldn't) be used.

🧠 1. Why Secondary Indexes?

  • Used when you want to query by a non-primary key column

  • Works best when:

    • Column has high cardinality (many unique values)
    • Queries are selective
  • Avoid on low-cardinality fields (e.g., status = 'active')

🏗️ 2. Create Secondary Index

➤ Syntax:

CREATE INDEX index_name ON keyspace.table (column_name);

✅ Example:

CREATE INDEX emp_dept_idx ON test_lab.employee (department);

🔍 3. Query Using the Index

SELECT * FROM test_lab.employee
WHERE department = 'HR';

⚠️ This query only works if a secondary index exists on department.

🔧 4. Drop Index

DROP INDEX IF EXISTS test_lab.emp_dept_idx;

📌 5. Index on Collection Columns

You can also index elements inside collections.

✅ Example – Indexing a map value:

CREATE TABLE test_lab.student_marks (
  student_id uuid PRIMARY KEY,
  marks map<text, int>
);

CREATE INDEX marks_index ON test_lab.student_marks (ENTRIES(marks));

Then:

SELECT * FROM test_lab.student_marks 
WHERE marks['math'] = 90;

🚫 Limitations of Indexes

  • May not scale well in high-volume systems
  • Better to model your data around queries than rely on indexes
  • Don't use multiple indexes in the same query

🧪 Day 6 Lab Tasks

  1. Add a secondary index on department in employee
  2. Query using that column (WHERE department = 'HR')
  3. Drop the index
  4. Create a new table with a map collection and index it
  5. Try a query on the map key using ENTRIES()

✅ Checklist

Task Done
Created and used secondary index ✅
Queried by non-primary column using index ✅
Dropped the index ✅
Created index on map collection column ✅

⚙️ Bonus Tip – Check Index Info:

DESCRIBE INDEX emp_dept_idx;


Tags