Section 8: Data Operations

Master SELECT Queries

Complete guide to querying data in Cassandra - WHERE clauses, filtering, ordering, LIMIT, aggregations, and performance optimization!

Home › Section 8: Data Operations › SELECT Data

🎯 SELECT in Cassandra

CRITICAL: SELECT is NOT Like SQL!

Cassandra's SELECT has SEVERE restrictions compared to SQL. You MUST query by partition key, and most WHERE clauses won't work without proper indexes or ALLOW FILTERING!

⚠️ The Golden Rule:

SELECT queries MUST include the partition key (or use secondary indexes/materialized views)
Without partition key = FULL CLUSTER SCAN = Performance disaster!

✅ What Works Well

  • Query by partition key
  • Query with clustering columns (in order)
  • LIMIT and pagination
  • ORDER BY (if matches clustering)
  • Simple aggregations (COUNT, SUM)

❌ What Doesn't Work

  • JOINs (not supported!)
  • Complex WHERE without index
  • GROUP BY (limited)
  • Subqueries
  • OR conditions

📖 Basic SELECT Syntax

Complete SELECT Syntax

Full Syntax
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY clustering_column [ASC|DESC]]
[LIMIT number]
[ALLOW FILTERING];

-- Aggregations
SELECT COUNT(*), SUM(column), AVG(column), MIN(column), MAX(column)
FROM table_name
[WHERE partition_key = ?];

Simple Examples

-- Select all columns
SELECT * FROM users WHERE user_id = uuid('...');

-- Select specific columns
SELECT username, email FROM users WHERE user_id = ?;

-- Count rows
SELECT COUNT(*) FROM users WHERE user_id = ?;

-- With LIMIT
SELECT * FROM posts WHERE user_id = ? LIMIT 10;

🔄 How SELECT Query Works

SELECT Query Execution Flow 1️⃣ Client Sends Query SELECT * FROM users WHERE user_id=? 2️⃣ Coordinator: Parse & Validate ✓ Check table exists ✓ Validate WHERE clause (has partition key?) 3️⃣ Hash Partition Key → Find Node hash(user_id) = -5234567890 → Node 2 💾 4️⃣ Node 2 Reads Data Check Memtable → Check SSTables Apply clustering filters 5️⃣ Results Returned ✅ Data sent back to client ⚡ Performance Keys • Partition key = FAST • Single node lookup • No full table scan • Millisecond response ⚠️ Without Partition Key • Scans ALL nodes • Reads ALL SSTables • VERY slow • Can timeout

🔍 WHERE Clause Rules

WHERE Clause Restrictions

Cassandra's WHERE clause is MUCH more limited than SQL. You must follow specific rules or queries will fail!

✅ Valid WHERE Clauses

-- ✅ By partition key
WHERE user_id = ?

-- ✅ Partition + clustering (in order)
WHERE user_id = ?
  AND post_date = '2025-01-15'

-- ✅ With range on last clustering
WHERE user_id = ?
  AND post_date > '2025-01-01'

❌ Invalid WHERE Clauses

-- ❌ Missing partition key
WHERE username = 'alice'
ERROR!

-- ❌ Skipping clustering column
WHERE user_id = ?
  AND post_content LIKE '%hello%'
ERROR!

WHERE Clause Rules Explained

Rule Explanation Example
Rule 1 MUST include partition key WHERE user_id = ?
Rule 2 Clustering columns in LEFT-TO-RIGHT order Can't use month without year
Rule 3 Range queries ONLY on last clustering column WHERE user_id=? AND date > ?
Rule 4 Equality (=) before ranges (>, <) Can't have year > X AND month = Y
Rule 5 No OR conditions Must use IN or multiple queries

Complete WHERE Examples

-- Table structure
CREATE TABLE user_posts (
  user_id UUID,
  post_year INT,
  post_month INT,
  post_id UUID,
  content TEXT,
  PRIMARY KEY (user_id, post_year, post_month, post_id)
);

-- ✅ VALID: Partition key only
SELECT * FROM user_posts
WHERE user_id = ?;

-- ✅ VALID: Partition + first clustering
SELECT * FROM user_posts
WHERE user_id = ? AND post_year = 2025;

-- ✅ VALID: All in order with range on last
SELECT * FROM user_posts
WHERE user_id = ?
  AND post_year = 2025
  AND post_month = 1
  AND post_id > ?;

-- ❌ INVALID: Skipped post_year
SELECT * FROM user_posts
WHERE user_id = ? AND post_month = 1;
// ERROR: Cannot skip clustering columns!

-- ❌ INVALID: Range on non-last clustering
SELECT * FROM user_posts
WHERE user_id = ?
  AND post_year > 2020
  AND post_month = 1;
// ERROR: Range must be on LAST clustering column!

⚡ ALLOW FILTERING - Dangerous!

ALLOW FILTERING Can Kill Your Cluster!

ALLOW FILTERING bypasses normal query restrictions by scanning ALL data. This causes FULL TABLE SCANS which can:

  • 💀 Timeout queries
  • 💀 Crash nodes (out of memory)
  • 💀 Slow down entire cluster
  • 💀 Block other queries

What is ALLOW FILTERING?

-- Without ALLOW FILTERING (query fails)
SELECT * FROM users
WHERE username = 'alice';

ERROR: Cannot execute this query as it might involve data filtering
and thus may have unpredictable performance. Use ALLOW FILTERING.

-- With ALLOW FILTERING (works but DANGEROUS)
SELECT * FROM users
WHERE username = 'alice'
ALLOW FILTERING;

// ⚠️ Scans EVERY row in EVERY partition!
// If table has 10M rows = reads 10M rows!
⚠️ ALLOW FILTERING Visualization ✅ Normal Query (by partition key) SELECT * WHERE user_id = 'alice' Read 1 partition Not scanned ✓ Time: ~5ms • Memory: Low VS ❌ With ALLOW FILTERING SELECT * WHERE age > 30 ALLOW FILTERING Scans ALL partitions! Reads EVERY row Time: seconds/minutes • Memory: HIGH 🚨 When is ALLOW FILTERING Acceptable? ✅ Small tables (<1000 rows) in development ✅ Analytics queries on small datasets ✅ ONE-TIME data migrations (not in production queries!) ❌ NEVER in production with large tables ❌ NEVER in customer-facing queries ❌ NEVER without LIMIT (at minimum)

Better Alternatives to ALLOW FILTERING

  1. Redesign table: Make the queried column part of primary key
  2. Secondary Index: Create index on the column
  3. Materialized View: Create denormalized view
  4. Application-side filtering: Read by partition key, filter in code

📊 ORDER BY - Clustering Order Only!

ORDER BY is Extremely Limited

You can ONLY ORDER BY clustering columns, and it must match the table's defined clustering order (or be reversed)!

ORDER BY Rules

-- Table with clustering order
CREATE TABLE user_posts (
  user_id UUID,
  post_date TIMESTAMP,
  content TEXT,
  PRIMARY KEY (user_id, post_date)
) WITH CLUSTERING ORDER BY (post_date DESC);

-- ✅ Default order (matches clustering order DESC)
SELECT * FROM user_posts
WHERE user_id = ?;
// Returns newest posts first automatically

-- ✅ Explicit DESC (same as default)
SELECT * FROM user_posts
WHERE user_id = ?
ORDER BY post_date DESC;

-- ✅ Reverse order (ASC when table is DESC)
SELECT * FROM user_posts
WHERE user_id = ?
ORDER BY post_date ASC;
// Returns oldest posts first

-- ❌ INVALID: Can't order by non-clustering column
SELECT * FROM user_posts
WHERE user_id = ?
ORDER BY content;
ERROR! Cannot order by non-clustering column
How ORDER BY Works in Cassandra Data Stored on Disk (Already Sorted!) Partition: user_id = 'alice' post_date: 2025-01-15 | "Latest post" 1st post_date: 2025-01-10 | "Recent post" 2nd post_date: 2025-01-05 | "Older post" 3rd post_date: 2024-12-20 | "Old post" 4th post_date: 2024-12-01 | "Oldest post" 5th Query Results ORDER BY post_date DESC (default) 1. 2025-01-15 | "Latest post" 2. 2025-01-10 | "Recent post" 3. 2025-01-05 | "Older post" 4. 2024-12-20 | "Old post" 5. 2024-12-01 | "Oldest post" ORDER BY post_date ASC (reversed) 1. 2024-12-01 | "Oldest post" 2. 2024-12-20 | "Old post" 3. 2025-01-05 | "Older post" 4. 2025-01-10 | "Recent post" 5. 2025-01-15 | "Latest post" 💡 Key Point: Data Already Sorted on Disk! ORDER BY is FREE (no sorting needed) - just read in order or reverse order

ORDER BY Best Practices

  • Choose clustering order carefully: Pick DESC if you'll usually want newest first
  • Don't specify ORDER BY if using default: Saves typing, same result
  • Reversing is cheap: Reading backwards is just as fast as forwards
  • Can't order by regular columns: Redesign table or use materialized view

🔢 LIMIT & Pagination

LIMIT Basics

-- Get first 10 rows
SELECT * FROM user_posts
WHERE user_id = ?
LIMIT 10;

-- Get first 100 rows
SELECT * FROM user_posts
WHERE user_id = ?
LIMIT 100;

-- LIMIT is applied AFTER filtering
SELECT * FROM user_posts
WHERE user_id = ?
  AND post_date > '2025-01-01'
LIMIT 20;

Pagination with Paging State

-- Page 1: Get first 10 posts
SELECT * FROM user_posts
WHERE user_id = ?
LIMIT 10;

// Driver returns: results + paging_state token
// paging_state = "eyJjb2x1bW4...encrypted token..."

-- Page 2: Pass paging_state to get next 10
// Application uses driver method:
// session.execute(query, paging_state=previous_paging_state)

-- ⚠️ CQL doesn't have OFFSET!
-- Must use paging_state from driver
Pagination Flow in Cassandra All Records in Partition (user_id = 'alice', 50 posts) Page 1: Records 1-10 Post 1, Post 2, Post 3... ...Post 9, Post 10 Page 2: Records 11-20 Post 11, Post 12, Post 13... ...Post 19, Post 20 Page 3: Records 21-30 Post 21, Post 22, Post 23... ...Post 29, Post 30 Pages 4-5: Records 31-50 Request 1 SELECT * WHERE user_id=? LIMIT 10; Response 1 ✓ Records: Post 1-10 ✓ paging_state: "abc123..." (Token points to Post 11) 📄 Display Page 1 to user Request 2 (Next Page) Same query + LIMIT 10 + paging_state="abc123..." Response 2 ✓ Records: Post 11-20 ✓ paging_state: "def456..." 📄 Display Page 2 to user Request 3 (Next Page) Same query + LIMIT 10 + paging_state="def456..." Response 3 ✓ Records: Post 21-30 ✓ paging_state: "ghi789..." 📄 Display Page 3 to user 💡 Pagination Best Practices ✓ Always use paging_state (driver handles it) • ✓ Consistent page size • ❌ Don't use OFFSET (not supported!)

Important LIMIT Notes

  • No OFFSET: Cassandra doesn't support OFFSET - must use paging state
  • Paging state is opaque: Don't parse or manipulate it - treat as black box
  • Can't jump to page N: Must page through sequentially (1→2→3→...)
  • Stateless pagination: Store paging_state in UI/cookie to continue

📈 Aggregation Functions

Available Aggregation Functions

-- COUNT: Count rows
SELECT COUNT(*) FROM user_posts
WHERE user_id = ?;

-- SUM: Total of numeric column
SELECT SUM(views) FROM user_posts
WHERE user_id = ?;

-- AVG: Average value
SELECT AVG(likes) FROM user_posts
WHERE user_id = ?;

-- MIN & MAX: Minimum and maximum
SELECT MIN(created_at), MAX(created_at)
FROM user_posts
WHERE user_id = ?;

-- Multiple aggregations together
SELECT COUNT(*), SUM(views), AVG(likes)
FROM user_posts
WHERE user_id = ?;

Aggregation Limitations

  • No GROUP BY (usually): Aggregations work on entire result set, not groups
  • Must include partition key: Can't aggregate across all partitions efficiently
  • COUNT(*) can be expensive: Counts every row in partition
  • Per-partition only: Can't easily get global aggregations
Function Description Example Performance
COUNT(*) Count all rows How many posts does user have? ⚠️ Can be slow on large partitions
SUM(col) Sum numeric values Total views for user's posts ⚠️ Reads all rows
AVG(col) Average value Average likes per post ⚠️ Reads all rows
MIN(col) Minimum value Earliest post date ✓ Fast if clustering column
MAX(col) Maximum value Latest post date ✓ Fast if clustering column

💡 Real-World SELECT Examples

Example 1: Social Media - User Feed

Requirement: Show user's latest 20 posts with pagination

-- Table design
CREATE TABLE user_timeline (
  user_id UUID,
  post_id TIMEUUID,
  content TEXT,
  likes INT,
  PRIMARY KEY (user_id, post_id)
) WITH CLUSTERING ORDER BY (post_id DESC);

-- Query: Get latest 20 posts
SELECT post_id, content, likes
FROM user_timeline
WHERE user_id = ?
LIMIT 20;

-- Query: Get posts older than specific post_id (pagination)
SELECT post_id, content, likes
FROM user_timeline
WHERE user_id = ?
  AND post_id < ? -- "Load more" pagination
LIMIT 20;

-- Query: Count total posts
SELECT COUNT(*) FROM user_timeline
WHERE user_id = ?;

Example 2: E-commerce - Order History

Requirement: Find user's orders from last 30 days

-- Table design
CREATE TABLE orders_by_user (
  user_id UUID,
  order_date DATE,
  order_id UUID,
  total DECIMAL,
  status TEXT,
  PRIMARY KEY (user_id, order_date, order_id)
) WITH CLUSTERING ORDER BY (order_date DESC);

-- Query: Last 30 days of orders
SELECT * FROM orders_by_user
WHERE user_id = ?
  AND order_date >= '2024-12-01'
  AND order_date <= '2024-12-31';

-- Query: Total spent in December
SELECT SUM(total) AS total_spent
FROM orders_by_user
WHERE user_id = ?
  AND order_date >= '2024-12-01'
  AND order_date <= '2024-12-31';

-- Query: Orders with specific status (requires filtering)
SELECT * FROM orders_by_user
WHERE user_id = ?
  AND order_date >= '2024-12-01'
  AND status = 'shipped'
ALLOW FILTERING; -- ⚠️ Only if date range is small

Example 3: IoT - Sensor Data Query

Requirement: Query sensor readings for specific time range

-- Table design
CREATE TABLE sensor_data (
  sensor_id UUID,
  reading_date DATE,
  reading_time TIMESTAMP,
  temperature FLOAT,
  humidity FLOAT,
  PRIMARY KEY ((sensor_id, reading_date), reading_time)
);

-- Query: Today's readings
SELECT * FROM sensor_data
WHERE sensor_id = ?
  AND reading_date = '2025-01-15';

-- Query: Readings between specific times
SELECT * FROM sensor_data
WHERE sensor_id = ?
  AND reading_date = '2025-01-15'
  AND reading_time >= '2025-01-15 08:00:00'
  AND reading_time <= '2025-01-15 12:00:00';

-- Query: Average temperature today
SELECT AVG(temperature) AS avg_temp
FROM sensor_data
WHERE sensor_id = ?
  AND reading_date = '2025-01-15';

Example 4: Gaming - Leaderboard Query

Requirement: Get top 100 players by score

-- Table design
CREATE TABLE leaderboard (
  game_id UUID,
  season INT,
  score BIGINT,
  player_id UUID,
  player_name TEXT,
  PRIMARY KEY ((game_id, season), score, player_id)
) WITH CLUSTERING ORDER BY (score DESC);

-- Query: Top 100 players
SELECT player_name, score
FROM leaderboard
WHERE game_id = ?
  AND season = 2025
LIMIT 100;

-- Query: Players with score > 1000
SELECT player_name, score
FROM leaderboard
WHERE game_id = ?
  AND season = 2025
  AND score >= 1000;

-- Query: Player count above 1000
SELECT COUNT(*) FROM leaderboard
WHERE game_id = ?
  AND season = 2025
  AND score >= 1000;

⚡ Performance Optimization Tips

✅ Fast Queries

  • ✓ Always include partition key
  • ✓ Use clustering columns in order
  • ✓ Use LIMIT to reduce data transfer
  • ✓ Select only needed columns
  • ✓ Use prepared statements
  • ✓ Query single partition

❌ Slow Queries

  • ✗ Missing partition key
  • ✗ ALLOW FILTERING on large tables
  • ✗ SELECT * without LIMIT
  • ✗ COUNT(*) on huge partitions
  • ✗ Querying non-indexed columns
  • ✗ Multiple partition reads

Performance Best Practices

  1. Design for queries: Create tables based on how you'll query, not data relationships
  2. Use prepared statements: 10-100x faster than regular statements
  3. Specify columns: Don't use SELECT * in production
  4. Limit results: Always use LIMIT to prevent huge result sets
  5. Avoid ALLOW FILTERING: Redesign table or use secondary index instead
  6. Monitor partition sizes: Keep partitions under 100MB
  7. Use IN sparingly: IN queries can hit multiple nodes
  8. Batch smartly: Only batch writes to same partition

Query Performance Comparison

Query Type Performance Reason
WHERE partition_key = ? ⚡⚡⚡ FAST (~1-5ms) Single node, single partition
+ clustering columns ⚡⚡⚡ FAST (~1-10ms) Filters within partition
+ LIMIT 10 ⚡⚡⚡ FAST (~1-5ms) Stops after 10 rows
IN (pk1, pk2, pk3) ⚠️ MEDIUM (~10-50ms) Multiple partitions/nodes
ALLOW FILTERING (small) ⚠️ SLOW (100ms-1s) Scans partition
ALLOW FILTERING (large) 💀 VERY SLOW (seconds+) Full table scan
No partition key 💀 DISASTER (timeout) Scans entire cluster

Troubleshooting Slow Queries

If your SELECT query is slow:

  1. Check logs: Look for "Cannot execute this query" warnings
  2. Enable tracing: Use TRACING ON to see execution details
  3. Check partition size: Large partitions = slow reads
  4. Review data model: Might need denormalized table
  5. Add secondary index: If querying by non-PK column frequently
  6. Use materialized view: For complex query patterns
Previous
Insert Data
Advertisement

Responsive Ad