Maintaining order in MySQL "IN" query

Question

I have the following table

DROP TABLE IF EXISTS `test`.`foo`;
CREATE TABLE  `test`.`foo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Then I try to get records based on the primary key

SELECT * FROM foo f where f.id IN (2, 3, 1);

I then get the following result

+----+--------+
| id | name   |
+----+--------+
|  1 | first  |
|  2 | second |
|  3 | third  |
+----+--------+
3 rows in set (0.00 sec)

As one can see, the result is ordered by id. What I'm trying to achieve is to get the results ordered in the sequence I'm providing in the query. Given this example it should return

+----+--------+
| id | name   |
+----+--------+
|  2 | second |
|  3 | third  |
|  1 | first  |
+----+--------+
3 rows in set (0.00 sec)

Answer 1

The values in an IN() predicate are considered to be a set, and the result returned by an SQL query has no way to automatically infer order from that set.

In general, the order of any SQL query is arbitrary unless you specify an order with an ORDER BY clause.

You can use a MySQL function FIND_IN_SET() to do what you want:

SELECT * FROM foo f where f.id IN (2, 3, 1)
ORDER BY FIND_IN_SET(f.id, '2,3,1');

Note that the list argument to FIND_IN_SET() isn't a variable length list like the arguments of IN(). It has to be a string literal or a SET.

---

Re questions about performance: I'm curious too, so I tried both FIND_IN_SET() and FIELD() methods against my copy of the StackOverflow data:

With no index on VoteTypeId:

SELECT * FROM Votes ORDER BY FIND_IN_SET(VoteTypeId, '13,1,12,2,11,3,10,4,9,5,8,6,7');

3618992 rows in set (31.26 sec)
3618992 rows in set (29.67 sec)
3618992 rows in set (28.52 sec)

SELECT * FROM Votes ORDER BY FIELD(VoteTypeId, 13,1,12,2,11,3,10,4,9,5,8,6,7);

3618992 rows in set (37.30 sec)
3618992 rows in set (49.65 sec)
3618992 rows in set (41.69 sec)

With an index on VoteTypeId:

SELECT * FROM Votes ORDER BY FIND_IN_SET(VoteTypeId, '13,1,12,2,11,3,10,4,9,5,8,6,7');

3618992 rows in set (14.71 sec)
3618992 rows in set (14.81 sec)
3618992 rows in set (25.80 sec)

SELECT * FROM Votes ORDER BY FIELD(VoteTypeId, 13,1,12,2,11,3,10,4,9,5,8,6,7);

3618992 rows in set (19.03 sec)
3618992 rows in set (14.59 sec)
3618992 rows in set (14.43 sec)

Conclusion: with limited testing, there is no great advantage to either method.

Answer 2

As the other answer mentions: the query you posted has nothing about what order you'd like your results, just which results you'd like to get.

To order your results, I would use ORDER BY FIELD():

SELECT * FROM foo f where f.id IN (2, 3, 1)
ORDER BY FIELD(f.id, 2, 3, 1);

The argument list to FIELD can be variable length.