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
  • 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.

  • 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 clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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