ALLOW FILTERING: Filtering on Other Columns
In the WHERE lesson, Cassandra refused to filter on a column that is not part of the key:
SELECT name FROM employees
WHERE department = 'Sales';
Output:
InvalidRequest: Error from server: code=2200 [Invalid query] message="Cannot execute this query as it might involve data filtering and thus may have unpredictable performance. If you want to execute this query despite the performance unpredictability, use ALLOW FILTERING"
Read the end of the error message. It tells you how to run the query anyway: add ALLOW FILTERING.
SELECT name FROM employees
WHERE department = 'Sales'
ALLOW FILTERING;
Output:
name
-------
Henry
Frank
Lucy
John
(4 rows)
Now Cassandra runs the query and returns the 4 Sales employees.
What ALLOW FILTERING means
With ALLOW FILTERING you tell Cassandra: "Read as many rows as you need, check each one, and keep the ones that match. I accept that this can be slow." For this query, Cassandra reads every row of the table on every node and throws away the rows that are not in Sales.
Our practice table has 12 rows, so this takes no time at all. A real table can have a billion rows on 50 nodes. There, the same query reads all of them and slows down the whole cluster.
When is it OK?
- Quick queries on small tables, to look at your data
- A rare query for checking or fixing something by hand
In application code, avoid it. If your app often needs "employees by department", there are better ways:
- a secondary index on the column (in the Advanced Features section)
- a second table that has
departmentin its primary key (in the Architecture and Modeling section)
If you see ALLOW FILTERING in application code, it usually means a table is missing.
The employees table only has 12 rows, so filtering is fine here. In the exercise, find the IT employees.