Cassandra range search by secondary index with filtering enabled

Question

I play with Cassandra 3. I added a secondary index to the integer column, then I want to make a range query. First he made a mistake:

InvalidRequest: code=2200 [Invalid query] message="No supported secondary index found for the non primary key columns restrictions" 

So I added "Allow Filtering"

 cqlsh:mykeyspace> SELECT * FROM test ; id | id2 | age | extra ----+-----+-----+------- 1 | 1 | 1 | 1 2 | 2 | 2 | 2 (2 rows) cqlsh:mykeyspace > CREATE INDEX test_age on test (extra) ; cqlsh:mykeyspace > select * FROM test WHERE extra < 2 ALLOW FILTERING ; id | id2 | age | extra ----+------+-----+------- 1 | 1 | 1 | 1 2 | null | 2 | null (2 rows) 

Why is this going to happen? Is it design or is it a mistake?

Answer 1

With Cassandra 3.0+, you'll want to explore materialized views - they are designed for a more functional version of secondary indexes that work around most traditional crashes.

You almost never want to use traditional secondary indexes in high-performance production environments, and ALLOW FILTERING is usually terrible.

Answer 2

The answer is that Secondary Indexes is a bit of an anti-pattern. They do not behave like indexes in RDBMSs and basically support analytics queries. In essence, they require that all nodes be requested and have different response characteristics than regular Cassandra key searches. To discourage their inadvertent use, "ALLOW FILTERING" was added to make sure that the user knows that they are not performing a normal C * search.