The SORT operator is used to sort the input data.
Example: Sort the data in table t1, descending by column c1 and ascending by column c2.
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 sorts the data in table t1. The outputs & filters section of the execution plan shows the output information of the SORT operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the SORT operator, so the value is nil. |
| sort_keys([column, DESC],[column, ASC] ...) | Sorts by the column column.
sort_keys([t1.c1, DESC],[t1.c2, ASC]) specifies that the sort keys are columns c1 and c2, which are sorted in descending order and ascending order respectively. |
| prefix_pos(n) | The ordered position of the sort columns. For example, sort_keys([t1.c1, DESC], [t1.c2, ASC]),prefix_pos(1) indicates that the data of table t1 is ordered by column c1, and only column c2 needs to be sorted. n starts from 1, where 1 represents the first column in sort_keys. |
If a LIMIT clause (applicable only in MySQL-compatible mode) follows the ORDER BY clause, the optimizer further optimizes the execution plan by generating a TOP-N SORT operator, which uses heap sort to select the top N rows. 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 of the preceding example, topn(10) indicates the amount of data required for LIMIT 10.