-1

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.

subject-q
  • 91
  • 3
  • 19

1 Answers1

2

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    `field(id, 54, 23, 1)` is just short for `(CASE WHEN id = 54 THEN 1 WHEN id = 23 THEN 2 WHEN id = 1 THEN 3 ELSE 1)` the join is a bit complex. – Raymond Nijland Jul 10 '19 at 14:08
  • Does `order by field(id, 54, 23, 1);` reduce performance in any way? – subject-q Jul 10 '19 at 14:12
  • @RaymondNijland I have edited the question, please add more clarity – subject-q Jul 10 '19 at 14:22
  • *"Does order by field(id, 54, 23, 1); reduce performance in any way?"* Yes and no (if that makes sense) @ArunRajagopal FIELD should have the same performance as `CASE END` -> https://www.db-fiddle.com/f/wMp561HtNQGtkwjJRJ4nmb/0 as the explains are the same.. But still a sort algorithm (using filesort = quicksort) is needed for both which can be slow when more records needed to be fetched/sorted.. – Raymond Nijland Jul 10 '19 at 14:25