I am using cassandra 2.1.10. So First I will clear that I know secondary index are anti-pattern in cassandra.But for testing purpose I was trying following:
CREATE TABLE test_topology1.tt (
a text PRIMARY KEY,
b timestamp
) WITH bloom_filter_fp_chance = 0.01
AND caching = '{"keys":"ALL", "rows_per_partition":"NONE"}'
AND comment = ''
AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy'}
AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
AND dclocal_read_repair_chance = 0.1
AND default_time_to_live = 0
AND gc_grace_seconds = 864000
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND read_repair_chance = 0.0
AND speculative_retry = '99.0PERCENTILE';
CREATE INDEX idx_tt ON test_topology1.tt (b);
When I run following query it gives me error.
cqlsh:test_topology1> Select * from tt where b>='2016-04-29 18:00:00' ALLOW FILTERING;
InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'b >= <value>'"
while this Blog says that allow filtering can be used to query secondary index. Cassandra is installed on windows machine.
Range queries on secondary index columns are not allowed in Cassandra up to and including 2.2.x. However, as the post A deep look at the CQL WHERE clause points out, they are allowed on non-indexed columns, if filtering is allwed:
Direct queries on secondary indices support only =, CONTAINS or CONTAINS KEY restrictions.
[..]
Secondary index queries allow you to restrict the returned results using the =, >, >=, <= and <, CONTAINS and CONTAINS KEY restrictions on non-indexed columns using filtering.
So, given the table structure and index
CREATE TABLE test_secondary_index (
a text PRIMARY KEY,
b timestamp,
c timestamp
);
CREATE INDEX idx_inequality_test ON test_secondary_index (b);
the following query fails because the inequality test is done on the indexed column:
SELECT * FROM test_secondary_index WHERE b >= '2016-04-29 18:00:00' ALLOW FILTERING ;
InvalidRequest: code=2200 [Invalid query] message="No secondary indexes on the restricted columns support the provided operators: 'b >= <value>'"
But the following works because the inequality test is done on a non-indexed column:
SELECT * FROM test_secondary_index WHERE b = '2016-04-29 18:00:00' AND c >= '2016-04-29 18:00:00' ALLOW FILTERING ;
a | b | c
---+---+---
(0 rows)
This still works if you add another index on column c
, but also still requires the ALLOW FILTERING
term, which to me means that the index on column c is not used in this scenario.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With