Paged Queries

Phoenix supports standard SQL constructs to enable paged queries:

• Row Value Constructors (RVC)
• OFFSET with LIMIT

Row Value Constructors (RVC)

A row value constructor is an ordered sequence of values delimited by parentheses. For example:

(4, 'foo', 3.5)
('Doe', 'Jane')
(my_col1, my_col2, 'bar')

Just like regular values, row value constructors may be used in comparison expressions:

WHERE (x, y, z) >= ('foo', 'bar')
WHERE (last_name, first_name) = ('Jane', 'Doe')

Row value constructors are compared by conceptually concatenating the values together and comparing them against each other, with the leftmost part being most significant. Section 8.2 (comparison predicates) of the SQL-92 standard explains this in detail, but here are a few examples of predicates that would evaluate to true:

(9, 5, 3) > (8, 8)
('foo', 'bar') < 'g'
(1, 2) = (1, 2)

Row value constructors may also be used in an IN list expression to efficiently query for a set of rows given the composite primary key columns. For example, the following would be optimized to be a point get of three rows:

WHERE (x, y) IN ((1, 2), (3, 4), (5, 6))

Another primary use case for row value constructors is to support query-more type functionality by enabling an ordered set of rows to be incrementally stepped through. For example, the following query would step through a set of rows, 20 rows at a time:

SELECT title, author, isbn, description
FROM library
WHERE published_date > 2010
AND (title, author, isbn) > (?, ?, ?)
ORDER BY title, author, isbn
LIMIT 20

Assuming that the client binds the three bind variables to the values of the last row processed, the next invocation would find the next 20 rows that match the query. If the columns you supply in your row value constructor match in order the columns from your primary key (or from a secondary index), then Phoenix will be able to turn the row value constructor expression into the start row of your scan. This enables a very efficient mechanism to locate at or after a row.

OFFSET with LIMIT

Use OFFSET to specify the starting row offset into the result set returned by your query and LIMIT to specify the page size.

For example, if page size is 10, then to select the second page, the following query can be used (rows 11 to 20 are returned):

SELECT title, author, isbn, description
FROM library
WHERE published_date > 2010
ORDER BY title, author, isbn
LIMIT 10 OFFSET 10

OFFSET reads and skips rows on either the server or client depending on query type, whereas RVC is more effective for queries on the primary key axis because it can start directly from the provided key.