A WHERE clause restricts the rows that a CQL statement reads, updates, or deletes. In a SELECT, omitting it reads every row, which is simple but performs poorly across a distributed database. section 2.3.2 section 7.2.1
Use a WHERE clause with the table’s key structure in mind:
- Prefer the partition key so ScyllaDB can locate one partition instead of scanning every partition. section 2.3.2
- Add clustering keys according to the consecutive leftmost rule when narrowing rows within a partition. section 7.2.1
- Write the condition before an UPDATE operation to avoid accidentally changing rows across the cluster. section 2.3.3
- Make DELETE conditions as exact as possible, preferably using the partition key or full primary key. section 6.2.1
Filtering restaurant reviews
SELECT * FROM initial.food_reviews WHERE restaurant = 'Ernie''s Eats'; returns only Ernie’s Eats reviews. Adding ordered_at and food can identify one review precisely. A condition on ordered_at alone may require ALLOW FILTERING and can be rejected in future versions. section 2.3.2
Targeting writes
UPDATE authors SET bio = 'Alice loves writing about food.' WHERE id = eff9bd01-4a73-11ee-8863-c02b3f99d2b2; identifies Alice’s row for an update. DELETE FROM articles WHERE id = ...; removes one article, while DELETE title FROM articles WHERE id = ...; removes only its title. section 6.1.1 section 6.2.1
cqlsh> SELECT * FROM initial.food_reviews WHERE restaurant = 'Ernie''s Eats';
Browse the runnable restaurant-review query and its filtered output. section 2.3.2
