1

I'm building a MySQL query for a search function, now I want to order the outcome in a certain way using an array of ID's.

I've been able to do this by using a piece of MySQL similar to this:

SELECT id, name
FROM mytable 
WHERE id IN (77, 3, 123, 1) 
ORDER BY FIELD(id, 77, 3, 123, 1)

But I'm wondering if it's possible to Order by FIELD or by FIND_IN_SET without setting the IN() within the Select query.

I don't want to use the IN() statement because I also expect results that aren't in the array of ID's

Hopefully someone can push me in the right direction,

Dharman
  • 30,962
  • 25
  • 85
  • 135
Lennart
  • 639
  • 4
  • 14

2 Answers2

2
SELECT id, name
FROM mytable 
ORDER BY id NOT IN (77, 3, 123, 1),
         FIELD(id, 77, 3, 123, 1)
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Explanation needed. Learn [answer]. – noɥʇʎԀʎzɐɹƆ Jul 31 '16 at 02:31
  • This worked great, but unfortunately it has increased the load time of long queries by a second or more. Do you maybe know of any other ways of accomplishing this that might be faster ? – Lennart Jul 31 '16 at 03:28
  • 1
    @Lennart Can the longer query time has something to do with the change that you now get all rows instead of just 4? – Solarflare Jul 31 '16 at 06:53
  • @Solarflare No if i remove the Order by query it'll retrieve the same results but will take around 0.8 to 1 second less than when i use this sorting method. I do have to say my array of id's is quite long so maybe that's why it's taking so much longer because the array gets added in twice. – Lennart Jul 31 '16 at 12:51
  • Thanks allot for your answer Juergen d, i've chosen Solarflare's answer because he added a explanation but i really appreciated your fast respons. It really helped me out exactly at the moment I needed help, Thanks again for this! – Lennart Jul 31 '16 at 18:44
2

If you prefer just one evaluation, you can reverse your order in field() and use

SELECT id, name
FROM mytable 
ORDER BY FIELD(id, 1, 123, 3, 77) desc

It will still take considerable more time than your original query, because field() is more expensive than IN and you will now have to evaluate field() for every row instead of just some rows.

An alternative might be to use

SELECT id, name, FIELD(id, 77, 3, 123, 1) as orderno
FROM mytable 
WHERE id IN (77, 3, 123, 1) 
union 
SELECT id, name, 99999999
FROM mytable 
WHERE id NOT IN (77, 3, 123, 1)
order by orderno, id

This can be faster depending on the size of your array, but you would have to test it, it might as well be worse.

Solarflare
  • 10,721
  • 2
  • 18
  • 35
  • You should use `UNION ALL` because the two selects have definitely an empty intersection. – Paul Spiegel Jul 31 '16 at 16:57
  • The first method worked best for me, it was only a couple of milliseconds faster than with the NOT IN query but everything counts:P Thanks allot for your answer and explanation! – Lennart Jul 31 '16 at 18:36