Use MySQL IN condition in FIND_IN_SET

Question

I have a query where I use a pretty intensive WHERE IN clause to get a list of ID's related to players.

SELECT p.name,
  0a.stat_value
FROM leaderheadsplayers p
LEFT JOIN leaderheadsplayersdata_alltime 0a
ON 0a.player_id    = p.player_id
AND 0a.stat_type   = 'kills'
WHERE p.player_id IN
  (SELECT player_id
  FROM
    (SELECT 0a.player_id
    FROM leaderheadsplayersdata_alltime 0a
    WHERE 0a.stat_type = 'kills'
    ORDER BY 0a.stat_value DESC LIMIT 0,
      20
    ) 1a
  )

The problems is that I want to keep the order of these ID's from the IN clause in my final result by using a FIND_IN_SET without doing the intensive query from the IN clause again. Something like this:

SELECT p.name,
  0a.stat_value
FROM leaderheadsplayers p
LEFT JOIN leaderheadsplayersdata_alltime 0a
ON 0a.player_id    = p.player_id
AND 0a.stat_type   = 'kills'
WHERE p.player_id IN
  (SELECT player_id
  FROM
    (SELECT 0a.player_id
    FROM leaderheadsplayersdata_alltime 0a
    WHERE 0a.stat_type = 'kills'
    ORDER BY 0a.stat_value DESC LIMIT 0,
      20
    ) 1a
  )
ORDER BY FIND_IN_SET(p.player_id, result_from_in_clause)

This is my current output:

player_id | stat_value
3 | 304
5 | 507
4 | 208

This is what I want to get:

player_id | stat_value
5 | 507
3 | 304
4 | 208

When ever find_in_set comes up, I jump to the natural conclusion that your tables aren't normalized. http://stackoverflow.com/a/41215681/267540 http://stackoverflow.com/a/41305027/267540 — e4c5, Jan 01 '17 at 13:51
I don't see why my tables aren't normalized. I'm not doing any crazy stuff like saving a delimited list. — Robin De Baets, Jan 01 '17 at 13:55

Gurwinder Singh · Accepted Answer · 2017-01-01T14:06:26.550

1

Simple order by should be able able to do it:

SELECT p.name,
  0a.stat_value
FROM leaderheadsplayers p
LEFT JOIN leaderheadsplayersdata_alltime 0a
ON 0a.player_id    = p.player_id
AND 0a.stat_type   = 'kills'
WHERE p.player_id IN
  (SELECT player_id
  FROM
    (SELECT 0a.player_id
    FROM leaderheadsplayersdata_alltime 0a
    WHERE 0a.stat_type = 'kills'
    ORDER BY 0a.stat_value DESC LIMIT 0,
      20
    ) 1a
  )
order by 
  0a.stat_value,  p.player_id;

edited Jan 01 '17 at 14:06

answered Jan 01 '17 at 13:52

Gurwinder Singh

38,557
6
51
76

Won't this hurt the performance if 0a.stat_value is something like a sum or something? – Robin De Baets Jan 01 '17 at 13:56
I actually had to use that redundant subquery level because my MySQL version doesn't allow to use ORDER by in a IN clause. It's kind of a bypass. – Robin De Baets Jan 01 '17 at 14:03
@Robin I see. Does it produce the result as you expect? – Gurwinder Singh Jan 01 '17 at 14:07
1

Yes it does. I also had that solution but I was just wondering about the performance but it seems fine. – Robin De Baets Jan 01 '17 at 14:09

Use MySQL IN condition in FIND_IN_SET

1 Answers1