I understand how ORDER BY
clause works and how the FIELD()
function works.
What i want to understand is how the both of them work together to sort.
How are the rows retrieved and how is the sort order derived
+----+---------+
| id | name |
+----+---------+
| 1 | stan |
| 2 | kyle |
| 3 | kenny |
| 4 | cartman |
+----+---------+
SELECT * FROM mytable WHERE id IN (3,2,1,4) ORDER BY FIELD(id,3,2,1,4)
The query above will result in
+----+---------+
| id | name |
+----+---------+
| 3 | kenny |
| 2 | kyle |
| 1 | stan |
| 4 | cartman |
+----+---------+
something similar to saying ORDER BY 3, 2, 1, 4
QUESTIONS
- How does this work internally?
- How does MySQL get the rows, and calculate the sort order ?
- How does MySQL know it has to sort by the id column ?
Best Answer
For the record
should work as well because you do not have to order the list in the
WHERE
clauseAs for how it works,
FIELD() is a function that returns the index position of a comma-delimited list if the value you are searching for exists.
The
ORDER BY
values are evaluated by what FIELD() returnsYou can create all sorts of fancy orders
For example, using the IF() function
This will cause the first 4 ids to appear at the top of the list, Otherwise, it appears at the bottom. Why?
In the
ORDER BY
, you either get 0 or 1.Let's flip it with DESC in the first column
In the
ORDER BY
, you still either get 0 or 1.YOUR ACTUAL QUESTION
If you seriously want internals on this, goto pages 189 and 192 of the Book
for a real deep dive.
In essence, there is a C++ class called
ORDER *order
(TheORDER BY
expression tree). InJOIN::prepare
,*order
is used in a function calledsetup_order()
. Why in the middle of theJOIN
class? Every query, even a query against a single table is always processed as a JOIN (See my post Is there an execution difference between a JOIN condition and a WHERE condition?)The source code for all this is
sql/sql_select.cc
Evidently, the
ORDER BY
tree is going to hold the evaluation ofFIELD(id,3,2,1,4)
. Thus, the numbers 0,1,2,3,4 are the values being sorted while carrying a reference to the row involved.