liveBook · Manning

ALLOW FILTERING clause

As explained in ScyllaDB in Action

ALLOW FILTERING is a CQL query option that permits filtering operations that may require examining rows beyond the query's directly supported access path.

ALLOW FILTERING is a CQL option that lets ScyllaDB execute queries filtering on columns outside the supported primary-key access path. It is useful for unsupported query requirements, but can create unpredictable performance. section 7.1.1 section 7.2.3

Filtering authors by name

The authors table is keyed by id, so filtering directly by name is rejected unless explicitly enabled: SELECT * FROM reviews.authors WHERE name = 'Alice Author' ALLOW FILTERING;. The query may examine rows matching any supplied partition or clustering keys, then filter the remaining columns on the server. section 7.2.3

Assess the query before using the clause:

  • Keep the partition key in the WHERE clause whenever possible; omitting it can scan every row across every partition. section 7.2.3
  • Do not assume LIMIT makes an unrestricted filtered query cheap; ScyllaDB may still perform a large initial scan. section 7.2.3
  • Test the query rather than treating ALLOW FILTERING as a schema design; materialized views or indexes can support recurring access patterns. section 7.2.3 section 7.4
  • Use it cautiously with pattern matching: an indexed range scan avoids a full table scan but may still read across many partitions. section 7.3.3
cqlsh> SELECT * FROM reviews.authors
WHERE name LIKE 'Alice%' ALLOW FILTERING;  #1

@ Row 1
-------+--------------------------------------
 id    | eff9bd01-4a73-11ee-8863-c02b3f99d2b2
 bio   | Alice loves writing about food
 image | null
 name  | Alice Author

(1 rows)  #2

A runnable CQL example shows pattern matching with ALLOW FILTERING and the resulting author row. section 7.3.3

How ScyllaDB in Action covers it

How deep it goes
Explains server-side filtering and scan costs, but stops short of query-planning internals.
Examples
Demonstrates author-name equality filtering and an indexed Alice% pattern query.
What it assumes
Assumes basic CQL SELECT syntax and primary-key concepts while explaining unsupported filters from the query behavior.
What you can do after
Diagnose/debug filtered-query warnings and test whether a CQL query imposes unacceptable scan or cluster load.
How it connects
Connects filtering with primary keys, partition keys, clustering keys, indexes, and materialized views.
Cover of ScyllaDB in Action
liveBookmanning.com
Read ScyllaDB in Action on liveBook Get the book at manning.com

This explainer was generated by Manning's knowledge graph from the text of ScyllaDB in Action. The section links point to the parts of the book it draws on.