In Scylla DB, How can I query the records for multiple condition and without mentioning ALLOW FILTERING?

Question

I have a table in ScyllaDB:

CREATE TABLE taxiservice.operatoragentsauditlog (
    hourofyear int,
    operationtime bigint,
    action text,
    actiontype text,
    appname text,
    entityid text,
    entitytype text,
    operatorid text,
    operatoripaddress text,
    operatorname text,
    payload text,
    PRIMARY KEY (hourofyear, operationtime)
) WITH CLUSTERING ORDER BY (operationtime DESC)
    AND bloom_filter_fp_chance = 0.01
    AND caching = {'keys': 'ALL', 'rows_per_partition': 'ALL'}
    AND comment = ''
    AND compaction = {'class': 'LeveledCompactionStrategy'}
    AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
    AND crc_check_chance = 1.0
    AND dclocal_read_repair_chance = 0.0
    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 auditactiontype ON taxiservice.operatoragentsauditlog (actiontype);
CREATE INDEX auditid ON taxiservice.operatoragentsauditlog (entityid);
CREATE INDEX agentid ON taxiservice.operatoragentsauditlog (operatorid);
CREATE INDEX auditaction ON taxiservice.operatoragentsauditlog (action);

I have return the query:

select * from taxiService.operatoragentsauditlog
where hourOfYear =3655
  and actionType ='XYZ'
  and operatorId in ('100','200') limit 500;

And Scylla throwing the issue like :

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"

Here whatever I included column names in conditions are index's in the table, then also its throwing the above mentioned error.

How I can fetch the details without adding allow filtering in query.

All the Scylla Query Written with Allow Filters and I deployed changes in Production, then Server started throwing Service internal error(NoHostAvailableException) and its caused to fetch the data from scylla db.

How I can resolve the NoHostAvailableException In Scylla?

com.datastax.driver.core.exceptions.NoHostAvailableException: All host(s) tried for query failed (no host was tried)
at com.datastax.driver.core.exceptions.NoHostAvailableException.copy(NoHostAvailableException.java:83) ~[cassandra-driver-core-3.10.2.jar:?]
at com.datastax.driver.core.exceptions.NoHostAvailableException.copy(NoHostAvailableException.java:37) ~[cassandra-driver-core-3.10.2.jar:?]
at com.datastax.driver.core.DriverThrowables.propagateCause(DriverThrowables.java:35) ~[cassandra-driver-core-3.10.2.jar:?]
at com.datastax.driver.core.DefaultResultSetFuture.getUninterruptibly(DefaultResultSetFuture.java:293) ~[cassandra-driver-core-3.10.2.jar:?]
at com.datastax.driver.core.AbstractSession.execute(AbstractSession.java:58) ~[cassandra-driver-core-3.10.2.jar:?]
at com.datastax.driver.mapping.MethodMapper.invoke(MethodMapper.java:184) ~[cassandra-driver-mapping-3.10.2.jar:?]
at com.datastax.driver.mapping.AccessorInvocationHandler.invoke(AccessorInvocationHandler.java:67) ~[cassandra-driver-mapping-3.10.2.jar:?]
at com.sun.proxy.$Proxy161.getRideAuditLog(Unknown Source) ~[?:?]
at com.mycomany.myproduct.auditLog.AuditLogService.getRideAuditLog(AuditLogService.java:21) ~[taxiopconsoleservice-1.1.0.jar:?]

Answer 1

With distributed databases like Cassandra and Scylla, the idea is to build your tables to suit your queries. To that end, you could build another table and duplicate the data into it. In this new table, the primary key definition should look like this:

PRIMARY KEY (hourOfYear, actionType, operatorId)

That will support this query without the dreaded ALLOW FILTERING directive.

select * from taxiService.operatoragentsauditlog_by_hourofyear_and_actiontype
where hourOfYear =3655
  and actionType ='XYZ'
  and operatorId in ('100','200');

But as the original table is partitioned on hourOfYear, the query is restricted to a single partition. So even with ALLOW FILTERING it might not be that bad.

Answer 2

Your query

select * from taxiService.operatoragentsauditlog
where hourOfYear =3655
  and actionType ='XYZ'
  and operatorId in ('100','200') limit 500;

Will look at either the actionType or operatorId index to find all the rows matching the restriction on that column, and than need to go over all the candidate rows to check if they also match the two other restrictions. That's why you need ALLOW FILTERING. Theoretically, actionType = 'XYZ' may match a million rows, so this query will need to go over a million rows just to return a handful that match all the candiate rows.

Some search engines have an efficient way to intersect two index lookups - maybe actionType = 'XYZ' has a million matches, and operatorId in ('100', '200') has a million matches, but their intersection is just 10 rows. Search engines use a skip list mechanism to allow the intersection to be calculated efficiently. But Scylla doesn't have this feature. It will pick just one of the two indexes (you don't know which), and go over its matches one by one. By the way, please note that even if Scylla did support efficient index intersection, your hourOfYear = 3655 restriction isn't indexed, so would need row-by-row filtering anyway.

As Aaron noted in his answer, the solution can be to use ALLOW FILTERING if the one index match results in a small-enough number of matches (it would be better to have just one index, not two, so you'll know exactly which index is being used), or - change your table schema to something which better matches your queries. The latter is always good advice in many situations.

Regarding the NoHostAvailableException - it means the Scylla replicas failed to perform this query for some reason. It might indicate a bug, or a timeout (which would also be a bug, because a scan like your query should do paging - not time out). Please look at the Scylla log if there's an error message that appears at the time of this request, and report the problem in the Scylla bug tracker at https://github.com/scylladb/scylla/issues.