Is it possible get result set in the order of how it was specified in 'where in' clause?

Question

Assume there is a table employee.

mysql> select * from employee limit 3\G
*************************** 1. row ***************************
     id: 1
   name: consequatur
  phone: 245.939.07
address: 37613 Stiedemann Vista Suite 077
Port Imeldaside, WI 19791
*************************** 2. row ***************************
     id: 2
   name: placeat
  phone: (193)912-8
address: 9742 Feest Mill Suite 275
North Jo, MA 04371-5138
*************************** 3. row ***************************
     id: 3
   name: non
  phone: 1-598-109-
address: 72476 Haley Crest Apt. 698
Isabelberg, SD 20263-9817

Let us say it contains lot of data. I have gotten ids that I want to get data on. So I query and I get the following result set.

mysql> select e.id, e.name, e.phone from employee e where id in (54,2,16);
+----+-------------+------------+
| id | name        | phone      |
+----+-------------+------------+
|  2 | placeat     | (193)912-8 |
| 16 | consequatur | 245.939.07 |
| 54 | eum         | 629.885.95 |
+----+-------------+------------+
3 rows in set (0.00 sec)

Is it possible to get result set in the order of ids present where id in clause? So what I expect is:

+----+-------------+------------+
| id | name        | phone      |
+----+-------------+------------+
| 54 | eum         | 629.885.95 |
|  2 | placeat     | (193)912-8 |
| 16 | consequatur | 245.939.07 |
+----+-------------+------------+
3 rows in set (0.00 sec)

There is a sort criteria which returns the order 54,2,16 but assume the use case does not demand it to be run all the time. We get to know the order once and it is not likely to change. And assume that the phone is not indexed.

Also, please explain the performance of this method compared with having to do table scan and file sort it every time query is run.

Answer 1

No, but you can sort afterwards.

In your case, you could simply use:

order by id desc

A more general solution uses field():

select e.id, e.name, e.phone
from employee e
where id in (54, 23, 1)
order by field(id, 54, 23, 1);

Or join:

select e.id, e.name, e.phone
from employee e join
     (select 54 as id, 1 as ord union all
      select 23 as id, 2 as ord union all
      select 1 as id, 3 as ord
     ) i
     on e.id = i.id
order by i.ord;