Secondary Indexes: Filter on Non-Key Columns
There are three ways to filter by a column that is not part of the key. You already know ALLOW FILTERING. It reads the whole table, which is fine for small tests but slow in real apps. A second table with that column in its key is the usual Cassandra answer (you will learn this in the last section). This lesson shows the third way: a secondary index.
An index is an extra lookup list that Cassandra keeps for one column. It maps each value (like 'Sales') to the rows that have it.
Syntax
CREATE INDEX index_name ON table_name (column);
Example: index the department column
Create the index when the table is new, before you add data:
CREATE INDEX dept_idx ON employees (department);
Now add some employees:
INSERT INTO employees (id, name, department)
VALUES (1, 'Alice', 'HR');
INSERT INTO employees (id, name, department)
VALUES (3, 'John', 'Sales');
INSERT INTO employees (id, name, department)
VALUES (6, 'Frank', 'Sales');
INSERT INTO employees (id, name, department)
VALUES (12, 'Lucy', 'Sales');
Filter by department, with no ALLOW FILTERING:
SELECT name FROM employees
WHERE department = 'Sales';
Output:
name
-------
Frank
Lucy
John
(3 rows)
Cassandra keeps the index up to date on every insert, update and delete.
Why create the index first? When a table already has rows, Cassandra builds the index for them in the background. A query sent before the build is done can fail. In this course's editor, creating the index on the full 12-row employees table and querying it right away can fail with a ReadFailure error.
To remove an index, use its name:
DROP INDEX dept_idx;
When to use an index
A Cassandra index is not like an index in MySQL. Each node only indexes its own rows. So a query on an indexed column still has to ask every node in the cluster. It avoids reading every row, but not visiting every node.
An index works well when:
- the query is run sometimes, not on every page load
- each value matches a medium number of rows (a department is good)
It works badly for a column where every value is different, like an email. Every node is asked, just to find one row. It also works badly for a column with only two values, like true/false, because half the table matches.
If your app runs a query all the time, make a table for that query instead. Use indexes for the less frequent queries.
In the exercise, an index on department already exists. Find the names of all IT employees using it.