Sorting

When records are to be retrieved in a sorted order, indexes will speed up sorting.

If no appropriate indexes exist, qualifying records are located by performing a sequential search on the table. The resulting subset of records is sorted. The efficiency of the sort operation is affected by the Sort Space Size setting specified in the Configuration Utility.

If an index exists, a sequential search of the index (index walk) will be performed in some cases. For example, if an index exists on the PNO column, an index walk is performed to process the query:

SELECT * FROM part
    ORDER BY pno

In the following query, the index walk algorithm will make use of a compound index on the SNO and PNO columns:

SELECT * FROM partsupp
    ORDER BY sno, pno

When possible, the server uses the compound index if the first element in the compound index is referenced in a predicate or ORDER BY clause, even if the other columns are not referenced. For example, if the ORDER BY clause in the query above included only the SNO column, the compound index would be used. If the ORDER BY clause included only the PNO column, the index would not be used.

In the case of queries involving predicates and ORDER BY clauses, if appropriate indexes exist, the server will use the index to find the qualifying records, and then sort just those records. For example, given the following query, with indexes on PNO and QTY, the server uses the index on QTY to find the records that qualify, and then orders the result. The index on PNO is not currently used.

SELECT * FROM partsupp 
    WHERE qty >= 200 
    ORDER BY pno