This topic describes how to use the ORDER BY clause in SQL statements for Lindorm and provides the best practices for use in scenarios in which a large number of result entries are returned.

Scenarios

The ORDER BY clause in Lindorm SQL statements is commonly used in the following scenarios:
  • If the number of result entries is less than 100,000, ORDER BY can be used to query data in the memory without limits. In this case, you must enable the in-memory computing feature. To enable this feature, contact the technical support of Lindorm (DingTalk ID: s0s3eg3).
  • If the number of result entries is greater than or equal to 100,000, Lindorm imposes limits on the use of ORDER BY. The limits are affected by the data storage rules in Lindorm. When you execute ORDER BY, result entries are not sorted by the SQL engine. If you need to sort result entries based on a specific column, you can specify this column as the leftmost prefix column of the index.
    Warning If a large number of result entries are returned after in-memory computing is enabled, your client may stop responding.

Solutions

In most cases, after a table is created, you can sort data only with the limits. If you need to sort data based on a specific column, you must specify a single equality condition for the column. The following statements provide examples: 
CREATE TABLE test(p1, p2, p3, c1, c2, c3, primary key(p1, p2, p3))   // p1, p2, and p3 compose the primary key.
SELECT * FROM test WHERE p1=? ORDER BY p2                     // ORDER BY can be used in this SQL statement.
SELECT * FROM test WHERE p1=? and p2=? ORDER BY p3            // ORDER BY can be used in this SQL statement.
SELECT * FROM test WHERE p1=? and c1=? ORDER BY p2            // ORDER BY can be used in this SQL statement. An equality condition is specified for the primary key column p2.
SELECT * FROM test ORDER BY p1                                // ORDER BY can be used in this SQL statement. However, we recommend that you do not use ORDER BY in this manner.
SELECT * FROM test WHERE p1=? ORDER BY p3                     // ORDER BY cannot be used in this SQL statement. All primary key columns must be included in this statement.
SELECT * FROM test WHERE p1<? ORDER BY p2                     // ORDER BY cannot be used in this SQL statement. You must specify a single equality condition for the primary key column p1 in the WHERE clause.
SELECT * FROM test WHERE p1=? and p2>? ORDER BY p3            // ORDER BY cannot be used in this SQL statement. You must specify an equality condition for the primary key column p2 in the WHERE clause.
SELECT * FROM test WHERE p1=? and p2=? and p3=? ORDER BY c1   // ORDER BY cannot be used in this SQL statement. c1 is not a primary key column.
SELECT * FROM test WHERE p1=a or p1=b ORDER BY p2            // ORDER BY cannot be used in this SQL statement. Only a single value can be specified for a primary key column. In this statement, more than one value is specified for p1.
If you need to use ORDER BY to sort a large number of result entries, you can use one of the following solutions:
  • Change the primary key of your table.
  • Create a secondary index or a search index on the columns based on which you want to sort result entries. For SELECT * FROM test WHERE p1=? ORDER BY p3 in the preceding sample code, you can use CREATE INDEX idx ON test(p1, p3) to create a secondary index on the related columns. This way, result entries can be sorted based on p3.

Examples

The following sample code provides examples on how to use ORDER BY to sort a large number of result entries:

  • The secondary index and the data of the base table are stored in different tables. If you create a secondary index and use ORDER BY to sort result entries based on the index key columns of the secondary index, specify all index key columns in the order that is defined for the secondary index in the ORDER BY clause. Examples:
    // Sort result entries based on the c1 column.
SELECT * FROM test WHERE p1=? ORDER BY c1

// Solution: Create a secondary index to sort result entries based on the c1 column.
CREATE INDEX idx ON test(p1, c1)
  • You can achieve optimal performance by using a commonly used sorting method to sort data based on the index key columns of your secondary index. Examples:
    // Sort result entries based on the c1 column.
SELECT * FROM test WHERE p1=? ORDER BY c1 desc

// Solution: Create a secondary index to sort result entries based on the c1 column.
CREATE INDEX idx ON test(p1, c1 desc)
  • In the ORDER BY clause that includes multiple columns, specify the columns in the order that they appear in your base table and do not skip one or more of the columns. This way, you can achieve optimal performance. Examples:
    // Create a base table based on the specified schema.
CREATE TABLE test (p1 asc,p2 asc,p3,c1,c2)

// Sort result entries based on the c1 and c2 index key columns.
SELECT * FROM table WHERE p1 = ? ORDER BY c1 desc, c2 asc

// When you create a secondary index for sorting, specify the c1 and c2 index key columns in the same order that they appear in the ORDER BY clause.
CREATE INDEX idx ON test(p1, c1 desc, c2 asc)