Mysql: order by field or select union? 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.

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.