How does ORDER BY FIELD() in MySQL work internally

Question

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 ?

Answer 1

For the record

SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4);

should work as well because you do not have to order the list in the WHERE clause

As 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.

  • IF id = 1, then FIELD(id,3,2,1,4) returns 3 (position where 1 is in the list)
  • IF id = 2, then FIELD(id,3,2,1,4) returns 2 (position where 2 is in the list)
  • IF id = 3, then FIELD(id,3,2,1,4) returns 1 (position where 3 is in the list)
  • IF id = 4, then FIELD(id,3,2,1,4) returns 4 (position where 4 is in the list)
  • IF id = anything else, then FIELD(id,3,2,1,4) returns 0 (not in the list)

• The ORDER BY values are evaluated by what FIELD() returns

You can create all sorts of fancy orders

For example, using the IF() function

SELECT * FROM mytable
WHERE id IN (1,2,3,4)
ORDER BY IF(FIELD(id,3,2,1,4)=0,1,0),FIELD(id,3,2,1,4);

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.

• If the first column is 0, make any of the first 4 ids appear
• If the first column is 1, make it appear afterwards

Let's flip it with DESC in the first column

SELECT * FROM mytable
WHERE id IN (1,2,3,4)
ORDER BY IF(FIELD(id,3,2,1,4)=0,1,0) DESC,FIELD(id,3,2,1,4);

In the ORDER BY, you still either get 0 or 1.

• If the first column is 1, make anything but the first 4 ids appear.
• If the first column is 0, make the first 4 ids appear in the original order

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 (The ORDER BY expression tree). In JOIN::prepare, *order is used in a function called setup_order(). Why in the middle of the JOIN 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 of FIELD(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.

Answer 2

Maybe this will be too far from actual code so not low level enough from what you wanted:

When MySQL cannot use index to retrieve data in sorted order, it creates a temporary table/resultset with all selected columns and some additional data - one of those is some kind of a column for storing the results of ORDER BY expression value for each row - then it sends this tmp table to a "filesort" rutine with info which column to sort by. After that the rows are in sorted order so it can pick them one by one and return selected columns.