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 ?
  • 1
    try this variation of your query: SELECT *, FIELD(id,3,2,1,4) AS f FROM mytable WHERE id IN (3,2,1,4); Then add ORDER BY f or ORDER BY FIELD(id,3,2,1,4) and try again. – ypercubeᵀᴹ Aug 5 '15 at 20:10
up vote 41 down vote accepted

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.

  • 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

MySQL Internals

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.

  • 1
    This is an outstandingly excellent explanation. Using these methods I was able to get 3 orders, a primary first value that is the max of the set, then by the FIELD, then by another column for the ones not in the FIELD set. Something I wouldn't have dreamed of some time ago. Thanks for taking the time to really explain how this actually works. – Lizardx May 4 '16 at 23:03

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.