Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

As a newbie, I expected Mysql to return the rows of this query ordered by their ids order in the id clause

select * from test where id in (3,1,2)

Unfortunatelly for me, that's not true. The results comes in id asc. order.

I have read that the easiest way to get what I want is using order by field

select * from test where id in (3,1,2) order by field(id, 3,1,2)

But I also read that is not ver efficient for queries with lots of ids in the sorting.

So, Do you think that this query below would performance better?

select * from test where id=3 union select * from test where id=1 union select * from test where id=2
share|improve this question
If you need a specific order, you should specify it with the ORDER BY clause. – Max Vernon Apr 9 '13 at 15:55

If you need a specific order, you should specify it with the ORDER BY clause.

The most efficient way to perform an order-by is to ORDER BY somefield.

If you need those records sorted as 3,1,2, then perhaps you should add a field sortorder that is ordered the way you want so you can simply do ORDER BY sortorder.

share|improve this answer
My problem is that the order is dynamic (not random), it may change every time I run the query. – marquex Apr 9 '13 at 16:17
If you provide more detail, perhaps I can help you find a better solution. What are you really attempting to do? – Max Vernon Apr 9 '13 at 17:44
The order by field() clause does what I need, but by a filesort, what is not good when I repeat the operation so many times and with a lot of ids. I just wanted to know if there is a way to make it faster. I can't store the order in the database because that order is not always the same, depends on the user. Sometimes would be 1,2,3, others 2,3,1, others 1,2,3... but with hundreds of different ids each time. – marquex Apr 9 '13 at 17:54

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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