CODEWITHSUNDEEP
mysql
user2636556
user2636556

Reputation: 1915

Return results based on a custom order

I'm trying to do a simple search based on ID's

SELECT * FROM `test` AS a
WHERE a.bla != '1' AND (
   a.id = 85355 OR
   a.id = 85353 OR
   a.id = 80090 OR
   a.id = 1 OR
   a.id = 300
)
LIMIT 0, 5

The issue is that the results are not listed according to my query.

I want the row order to be like this 85355, 85353, 80090, 1, 300

I tried the following query but doesn't work.

SELECT * FROM `test` AS a WHERE a.bla!='1' AND (a.id=85355 OR a.id=85353 OR a.id=80090 OR a.id=1 OR a.id=300) 
ORDER BY IF (FIELD(id, "85355,85353,80090,1,300"), 0, id), FIELD(id, "85355,85353,80090,1,300")

Is there any way to sort the query results it according to my custom order?

Upvotes: 2

Views: 120

Answers (1)

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this:

SELECT * FROM `test` a
WHERE a.bla != '1' AND a.id in (85355, 85353, 80090, 1, 300)
ORDER BY FIELD(a.id, 85355, 85353, 80090, 1, 300)

For more information on the FIELD function check the official documentation.

Just to clarify, when you apply a function, a case statement or you use something other than the field itself in an ORDER BY clause you lose the ability to use the index (if any) on that field. Additionally, you're adding an extra calculation to be performed on the field, which also takes time to be performed. You will have to accept the fact that this will indeed result in a less performant query than one that uses ORDER BY a.id. However, it is clear that the latter will not fulfill the requirements of a custom sorting.

Anyway, as you are first filtering by a primary key with only 5 results you will at most match those 5 results. Ordering 5 results without using an index is trivial and should be resolved in a matter of milliseconds. Now, the more ids you add to the IN clause the more records you will have to order. If this number becomes significative then the query will begin to run slower. You will have to test based on your hardware and software configuration what significative means in your case.

PS: I've just realised that as id is most likely to be a primary key then you don't need the LIMIT 5 as the ids in the IN clause are already limiting them.

Upvotes: 3

Related Questions