Boost Large Dataset Query Speed with Cursor Pagination - Lindorm

OFFSET/LIMIT pagination scans and discards all rows before the requested page, so query time grows linearly with page depth. Cursor pagination avoids this by recording the position of the last row returned and starting the next query directly from that position. Use cursor pagination when conventional OFFSET/LIMIT pagination is too slow for your dataset size.

Prerequisites

Before you begin, ensure that you have:

LindormTable version 2.7.8 or later. To check the version and upgrade, see Release notes of LindormTable and Upgrade the minor engine version

Limitations

Cursor pagination supports only simple queries. Queries with aggregate functions (SUM, COUNT), ORDER BY clauses, or GROUP BY clauses are not supported.
Cursor pagination requires the OFFSET and LIMIT clauses.

Paginate through results

The pagination loop follows the same four steps for every query:

Run the first-page query with _l_next_cursor_ in the SELECT list (or use the /*+ _l_allow_cursor_ */ hint for wildcard queries).
From the result set, take the last _l_next_cursor_ value — this is the cursor for the next page.
Pass that cursor in the WHERE clause as _l_current_cursor_ = '<cursor value>' in subsequent queries.
Stop when _l_next_cursor_ returns null — you have reached the last page.

Choose the projection style that fits your query:

Style	When to use
Explicit projection (recommended)	You know which columns to return
Implicit projection	You need all columns, including dynamic ones (`SELECT *`)

Explicit projection (recommended)

Include _l_next_cursor_ directly in the column list.

First page:

SELECT <column_identifier1>, <column_identifier2>, _l_next_cursor_ FROM <table_identifier> LIMIT 0, 5;

The result includes a _l_next_cursor_ column containing a VARCHAR cursor value for each row.

Important

Each row in the result has a _l_next_cursor_ value. Use the value from the last row as the cursor for the next page.

Subsequent pages:

SELECT <column_identifier1>, <column_identifier2>, _l_next_cursor_ FROM <table_identifier> WHERE _l_current_cursor_ = '<cursor value>' AND <filter condition> LIMIT 5, 5;

In the WHERE clause, join the cursor condition and any original filter condition with AND.

Implicit projection

When the column list is *, LindormTable returns only predefined columns and excludes the cursor. To include the cursor in a wildcard query, add the /*+ _l_allow_cursor_ */ hint after SELECT.

First page:

SELECT /*+ _l_allow_cursor_ */ * FROM <table_identifier> LIMIT 0, 5;

Subsequent pages:

SELECT /*+ _l_allow_cursor_ */ * FROM <table_identifier> WHERE _l_current_cursor_ = '<cursor value>' AND <original filter condition> LIMIT 5, 5;

Note

Use implicit projection when you cannot determine the columns ahead of time, such as when querying dynamic columns.

Examples

The following examples use a sample table with 15 rows to demonstrate fetching three pages of five rows each.

Set up the sample table:

-- Create a sample table.
CREATE TABLE tb_cursor_test(c1 INT , c2 INT, c3 VARCHAR(50), PRIMARY KEY(c1));

-- Insert data into the table.
UPSERT INTO tb_cursor_test(c1, c3) VALUES (1, 'c3_1'),(2, 'c3_2'),(3, 'c3_3'),(4, 'c3_4'),(5, 'c3_5');
UPSERT INTO tb_cursor_test(c1, c3) VALUES (6, 'c3_6'),(7, 'c3_7'),(8, 'c3_8'),(9, 'c3_9'),(10, 'c3_10');
UPSERT INTO tb_cursor_test(c1, c3) VALUES (11, 'c3_11'),(12, 'c3_12'),(13, 'c3_13'),(14, 'c3_14'),(15, 'c3_15');

Step 1: Fetch page 1

Important

The first-page query must include _l_next_cursor_ in the SELECT list to initialize cursor pagination.

SELECT c1, c2, c3, _l_next_cursor_ FROM tb_cursor_test LIMIT 0, 5;

Output:

+----+------+------+------------------------------+
| c1 | c2   | c3   | _l_next_cursor_              |
+----+------+------+------------------------------+
| 1  | null | c3_1 | AAAABIAAAAYFAAAABk5PUk1BTA== |
| 2  | null | c3_2 | AAAABIAAAAYFAAAABk5PUk1BTA== |
| 3  | null | c3_3 | AAAABIAAAAYFAAAABk5PUk1BTA== |
| 4  | null | c3_4 | AAAABIAAAAYFAAAABk5PUk1BTA== |
| 5  | null | c3_5 | AAAABIAAAAYFAAAABk5PUk1BTA== |
+----+------+------+------------------------------+

The last row's _l_next_cursor_ value is AAAABIAAAAYFAAAABk5PUk1BTA==. Use this value to fetch page 2.

Step 2: Fetch page 2

SELECT c1, c2, c3, _l_next_cursor_ FROM tb_cursor_test WHERE _l_current_cursor_ = 'AAAABIAAAAYFAAAABk5PUk1BTA==' LIMIT 5, 5;

Output:

+----+------+-------+------------------------------+
| c1 | c2   | c3    | _l_next_cursor_              |
+----+------+-------+------------------------------+
| 6  | null | c3_6  | AAAABIAAAAsKAAAABk5PUk1BTA== |
| 7  | null | c3_7  | AAAABIAAAAsKAAAABk5PUk1BTA== |
| 8  | null | c3_8  | AAAABIAAAAsKAAAABk5PUk1BTA== |
| 9  | null | c3_9  | AAAABIAAAAsKAAAABk5PUk1BTA== |
| 10 | null | c3_10 | AAAABIAAAAsKAAAABk5PUk1BTA== |
+----+------+-------+------------------------------+

The next cursor is AAAABIAAAAsKAAAABk5PUk1BTA==. Use it to fetch page 3.

Step 3: Fetch page 3

SELECT c1, c2, c3, _l_next_cursor_ FROM tb_cursor_test WHERE _l_current_cursor_ = 'AAAABIAAAAsKAAAABk5PUk1BTA==' LIMIT 10, 5;

Output:

+----+------+-------+-----------------+
| c1 | c2   | c3    | _l_next_cursor_ |
+----+------+-------+-----------------+
| 11 | null | c3_11 | null            |
| 12 | null | c3_12 | null            |
| 13 | null | c3_13 | null            |
| 14 | null | c3_14 | null            |
| 15 | null | c3_15 | null            |
+----+------+-------+-----------------+

_l_next_cursor_ is null — this is the last page. Stop pagination here.