The SORT operator sorts input data.
Example: Sort the data in table t1, with column c1 sorted in descending order and column c2 in ascending order.
obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE INDEX i1 ON t1(c1);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT c1 FROM t1 ORDER BY c1 DESC, c2 ASC;
Query Plan:
================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------
|0 |SORT | |100000 |563390|
|1 | TABLE SCAN|T1(I1,Reverse)|100000 |385811|
================================================
Outputs & filters:
-------------------------------------
0 - output([T1.C1]), filter(nil), sort_keys([T1.C1, DESC], [T1.C2, ASC]), prefix_pos(1)
1 - output([T1.C1], [T1.C2]), filter(nil),
access([T1.C1], [T1.C2]), partitions(p0)
1 row in set
In the preceding example, Operator 0 SORT in the execution plan sorts data of table t1. The Outputs & filters section shows in detail the output information of the SORT operator.
| Field | Description |
|---|---|
| output | The output columns of the operator. |
| filter | The filter predicates of the operator. In this example, filter is set to nil because no filter condition is configured for the SORT operator. |
| sort_keys([column, DESC],[column, ASC] ...) | Sorts data by column.
sort_keys([t1.c1, DESC],[t1.c2, ASC]) indicates that the sort keys are the c1 and c2 columns. Column c1 is sorted in descending order, and column c2 in ascending order. |
| prefix_pos(n) | The position of the sorted column. For example, sort_keys([t1.c1, DESC], [t1.c2, ASC]),prefix_pos(1) indicates that data in table t1 is sorted in the c1 column. You need to sort only the c2 column. n starts from 1. The value 1 indicates the first column in sort_keys. |
If the ORDER BY block is followed by a LIMIT clause (applicable only to the MySQL mode), the optimizer further optimizes the execution plan and generates the TOP-N SORT operator. That is, the optimizer uses heap sorting to select TOP-N data. Here is an example:
obclient> EXPLAIN SELECT * FROM t1 WHERE c1 > 1 ORDER BY c1,c2 LIMIT 10;
Query Plan:
========================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
----------------------------------------
|0 |LIMIT | |10 |42432|
|1 | TOP-N SORT | |10 |42431|
|2 | TABLE SCAN|t1(i1)|10000 |41764|
========================================
Outputs & filters:
-------------------------------------
0 - output([t1.c1], [t1.c2]), filter(nil), limit(10), offset(nil)
1 - output([t1.c1], [t1.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC]), topn(10), prefix_pos(1)
2 - output([t1.c1], [t1.c2]), filter(nil),
access([t1.c1], [t1.c2]), partitions(p0)
1 row in set
In the execution plan display of the preceding example, topn(10) indicates the amount of data required by LIMIT 10.
