Practices of using paged queries to improve query performance - Hologres

Hologres supports paged queries in scenarios such as real-time reports. This topic describes the common paged query methods. You can select a paged query method based on your business requirements to improve query performance.

Overview

The following syntax is used to implement paged queries:

SELECT ...
FROM ...
ORDER BY key
LIMIT N OFFSET S;

Hologres performs the PARTIAL SORT operation on the scanned records by key to obtain the top N+S records, discards the top S records, and then returns the other N records.

The following parameters must be considered in a paged query method:

N: the quantity of records per page. Valid values: 10 to 1000.
S: the number of a record where a paged query starts. If the value of this parameter is large, the quantity of top N+S is large and the overheads of querying the top N+S records are large. If a paged query starts from page 1000, deep paging is required.
W: the size of each record. The copy operation needs to be performed during sorting. If the size of each record specified by the W parameter is large, the amount of data that is copied is large. This significantly affects query performance.

Method 1: Basic paged query

Scenario: The value of the S parameter is less than the product of 100 and N and the value of the W parameter is less than 10, in KB.
Sample statement:
Important
If the ORDER BY clause is not used, the output results are not sorted in order. Therefore, if you use the LIMIT N OFFSET S statement to perform paged queries twice, the output results may be duplicate or incomplete.
```
SELECT *
FROM table1
ORDER BY key1
LIMIT N OFFSET S;
```
This SQL statement is equivalent to the following SQL statements in MySQL:
```
-- The following SQL statements are available in MySQL:
SELECT * FROM table1 ORDER BY key1 LIMIT S, N;
SELECT * FROM table1 ORDER BY key1 LIMIT N OFFSET S;
```

Method 2: Implement paged queries based on a primary key

Scenario: Paged queries are performed on a table based on the primary key specified for the table.
Sample statement:
```
SELECT *
FROM table1
ORDER BY pk
LIMIT N OFFSET S;
```
If you specify a large value for the S or W parameter, you can execute the following statement for a paged query to save resources and improve query efficiency:
```
SELECT *
FROM table1
-- Replace the question mark (?) with the value of the primary key of the last record on the previous page.
WHERE pk > ?
ORDER BY pk
LIMIT N;
```
Note
Hologres starts to read data from the last record on the previous page based on the primary key. A smaller amount of data requires sorting because the data has been filtered during the scan. This improves query efficiency.

Method 3: Implement paged queries based on temporary tables

Scenario: Multiple tables are involved, and paged queries are performed on result sets that are obtained based on complex computing, such as result sets of the JOIN operation on multiple tables.

Sample statement:

SELECT *
FROM
 (
   ... Complex SQL subqueries
)
ORDER BY key1
LIMIT N OFFSET S;

In this scenario, complex SQL subqueries must be performed for each paged query request. This results in low query performance. We recommend that you write the results of complex SQL subqueries to a temporary table and perform a paged query on this temporary table. Perform the following steps to implement a paged query in this scenario:

Create a temporary table in advance to prevent Hologres from creating a temporary table for each subquery.

BEGIN
CREATE TABLE query_result_table_tmp(
  query_id text,
  key1 text,
  ...
  );
CALL set_table_property('query_result_table_tmp', 'distribution_key', 'query_id');
CALL set_table_property('query_result_table_tmp', 'clustering_key', 'query_id,key1');
CALL set_table_property('query_result_table_tmp', 'time_to_live', '24 hour');

Write the results of complex SQL subqueries to the temporary table.

INSERT INTO query_result_table_tmp(query_id, key1, ...)
-- Replace the question mark (?) with the value specified by the query_id parameter.
SELECT ?, key1, ... FROM (
  ... -- Complex SQL subqueries
  );

Execute the following statement to implement a paged query:

SELECT *
FROM query_result_table_tmp
ORDER BY key1
LIMIT N OFFSET S;