Cassandra SELECT Queries

The SELECT statement retrieves data from a Cassandra table. While CQL SELECT looks similar to SQL SELECT, Cassandra imposes important restrictions tied to its distributed architecture. Efficient queries always use the partition key; queries that skip it force Cassandra to scan every node in the cluster.

Basic SELECT Syntax

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

Select All Rows

SELECT * FROM customers;

The asterisk returns every column. In production, selecting all rows without a WHERE clause forces a full table scan — use it only in development or for small test tables.

Select Specific Columns

SELECT first_name, email FROM customers;

Listing specific columns reduces the amount of data transferred over the network, which improves performance for wide tables.

Select by Primary Key (Most Efficient)

SELECT * FROM customers
WHERE customer_id = 11111111-1111-1111-1111-111111111111;

Cassandra hashes the partition key and routes this query directly to the node that holds the data. This is a single-node read — extremely fast.

Select with Clustering Column Range

-- Table: orders_by_customer PRIMARY KEY (customer_id, order_date)

SELECT * FROM orders_by_customer
WHERE customer_id = [uuid]
  AND order_date >= '2024-01-01'
  AND order_date <  '2024-04-01';

This retrieves all orders for a customer placed in Q1 2024. Because all rows for the same customer live in one partition, Cassandra reads a contiguous range of disk storage — very efficient.

Disk layout for customer uuid-A:

[order_date: 2024-01-05] ──▶ read
[order_date: 2024-02-12] ──▶ read
[order_date: 2024-03-28] ──▶ read
[order_date: 2024-04-10] ──▶ stop (outside range)

SELECT with IN

The IN clause allows fetching rows for multiple specific partition key values in one query. Cassandra sends sub-queries to each relevant node in parallel.

SELECT * FROM customers
WHERE customer_id IN (
  uuid-A,
  uuid-B,
  uuid-C
);

Use IN sparingly. Querying more than 10–20 partition keys in one IN clause can cause coordinator memory pressure and slow down the cluster.

Aliases with AS

SELECT first_name AS name, email AS contact
FROM customers
WHERE customer_id = [uuid];

 name  | contact
───────────────────────────────
 Alice | alice@example.com

Functions in SELECT

toDate and toTimestamp

SELECT toDate(created_at) AS date_only FROM events
WHERE event_id = [uuid];

writetime

Returns the write timestamp of a column in microseconds — useful for debugging replication issues.

SELECT writetime(email) FROM customers
WHERE customer_id = [uuid];

 writetime(email)
──────────────────
 1710000000000000

ttl()

SELECT ttl(token_value) FROM session_tokens
WHERE token = [uuid];

COUNT

SELECT COUNT(*) FROM orders_by_customer
WHERE customer_id = [uuid];

 count
───────
 47

COUNT within a partition is efficient. Avoid COUNT(*) on an entire table — it forces a full cluster scan.

Token-Based Pagination

Cassandra does not support OFFSET-based pagination like SQL. Use the token function to paginate through partition keys efficiently.

-- Get the first page:
SELECT customer_id, first_name FROM customers LIMIT 10;

-- Get the next page starting after the last seen token:
SELECT customer_id, first_name FROM customers
WHERE token(customer_id) > token([last-seen-uuid])
LIMIT 10;

ALLOW FILTERING

When a WHERE clause filters on a column that is not part of the primary key, Cassandra requires ALLOW FILTERING to proceed. This tells Cassandra to scan every row in the table (or partition) and test the condition. It works but performs poorly on large tables.

-- Requires ALLOW FILTERING because 'email' is not a primary key:
SELECT * FROM customers
WHERE email = 'alice@example.com'
ALLOW FILTERING;

Use ALLOW FILTERING only on small, bounded datasets during development. For production, create a secondary index or redesign the table so the filter column becomes part of the primary key.

Distinct Partitions with DISTINCT

DISTINCT returns only the partition key columns without reading the full row. This is faster because Cassandra reads only the partition index, not the data cells.

SELECT DISTINCT customer_id FROM orders_by_customer;

Summary

SELECT in Cassandra works best when you provide the full partition key in the WHERE clause, routing the query to a single node. Clustering column ranges within a partition are fast sequential reads. Avoid full-table scans, use ALLOW FILTERING only on small datasets, and paginate large result sets using token-based pagination. Design your tables around the SELECT queries your application needs most.

Previous lesson

Back to course

Next lesson