All Products
Search

This Product

    Lindorm:Use ORDER BY in large result sets

    Document Center

    Lindorm:Use ORDER BY in large result sets

    Last Updated:Mar 28, 2026

    Lindorm stores data in primary key order. Because the SQL engine reads rows from storage in that physical order — without performing an in-memory sort — ORDER BY works efficiently only when the sort column aligns with the storage order. For queries returning fewer than 100,000 rows, ORDER BY works without restriction. For queries returning 100,000 or more rows, ORDER BY works only when each primary key column before the sort column has an equality condition in the WHERE clause.

    Warning

    Enabling in-memory computing for queries that return a large number of rows may cause your client to stop responding.

    To enable in-memory computing for queries returning fewer than 100,000 rows, contact Lindorm technical support (DingTalk ID: s0s3eg3).

    How ORDER BY works

    Lindorm physically stores rows in primary key order. The SQL engine reads rows in that order without an additional sort step, so ORDER BY is efficient only when query conditions align with storage order.

    For ORDER BY on a given column to work, every primary key column that precedes it in the key definition must have an exact equality condition in the WHERE clause. A range condition or an OR condition on any preceding column breaks the storage-order guarantee and makes ORDER BY invalid on subsequent columns.

    Consider a table with the primary key (p1, p2, p3):

    • WHERE p1=? ORDER BY p2 works — p1 has an equality condition, so rows within that p1 value are in p2 order.

    • WHERE p1=? ORDER BY p3 does not work — p2 has no equality condition, so p3 values are not contiguous.

    • WHERE p1<? ORDER BY p2 does not work — a range condition on p1 breaks the ordering guarantee.

    Supported and unsupported ORDER BY patterns

    The following table uses the schema CREATE TABLE test(p1, p2, p3, c1, c2, c3, PRIMARY KEY(p1, p2, p3)) to show which ORDER BY patterns work and which do not.

    SQL statementWorks?Reason
    SELECT * FROM test WHERE p1=? ORDER BY p2Yesp1 has an equality condition; p2 is the next prefix column
    SELECT * FROM test WHERE p1=? AND p2=? ORDER BY p3Yesp1 and p2 both have equality conditions; p3 is the sort column
    SELECT * FROM test WHERE p1=? AND c1=? ORDER BY p2Yesp1 has an equality condition; c1 is a non-key filter
    SELECT * FROM test ORDER BY p1Yes (not recommended)Full table scan with ORDER BY p1; avoid on large tables
    SELECT * FROM test WHERE p1=? ORDER BY p3Nop2 has no equality condition — p3 values are not in order
    SELECT * FROM test WHERE p1<? ORDER BY p2NoRange condition on p1 breaks the ordering guarantee
    SELECT * FROM test WHERE p1=? AND p2>? ORDER BY p3NoRange condition on p2 breaks the ordering guarantee
    SELECT * FROM test WHERE p1=? AND p2=? AND p3=? ORDER BY c1Noc1 is not a primary key column; no index covers it
    SELECT * FROM test WHERE p1=a OR p1=b ORDER BY p2NoOR conditions expand to multiple ranges; single-value equality required

    Sort by a non-primary-key column

    To sort results by a column that is not part of the primary key, create a secondary index that covers the sort column. The secondary index stores data in the order defined by its index key columns, so ORDER BY on those columns becomes a sequential scan rather than an in-memory sort.

    When using ORDER BY with a secondary index, list all index key columns in the ORDER BY clause in the exact order they are defined in the index.

    Sort by a non-primary-key column

    -- Target query: sort by c1
SELECT * FROM test WHERE p1=? ORDER BY c1

-- Solution: create a secondary index with p1 and c1 as index key columns
CREATE INDEX idx ON test(p1, c1)

    Sort in descending order

    Match the sort direction in the index definition to the ORDER BY clause.

    -- Target query: sort by c1 in descending order
SELECT * FROM test WHERE p1=? ORDER BY c1 DESC

-- Solution: declare c1 as DESC in the index definition
CREATE INDEX idx ON test(p1, c1 DESC)

    Multi-column ORDER BY

    List all sort columns in the ORDER BY clause in the same order they appear in the index, with no skipped columns.

    -- Base table schema
CREATE TABLE test (p1 ASC, p2 ASC, p3, c1, c2)

-- Target query: sort by c1 and c2
SELECT * FROM test WHERE p1=? ORDER BY c1 DESC, c2 ASC

-- Solution: define index key columns in the same order and direction
CREATE INDEX idx ON test(p1, c1 DESC, c2 ASC)

    Workarounds for unsupported ORDER BY patterns

    If ORDER BY on a required column does not align with the primary key and a secondary index cannot satisfy it, two options are available:

    • Change the primary key. Redesign the table schema so the sort column is part of the primary key in the required position.

    • Create a secondary index or search index. Add a secondary index that includes the WHERE clause columns followed by the sort column. The secondary index stores data in a separate table from the base table. For example, to support SELECT * FROM test WHERE p1=? ORDER BY p3, create CREATE INDEX idx ON test(p1, p3).