How does ORDER BY FIELD() in MySQL work internally

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