Mysql – Select Records that match ALL groups in a many to many join table

mysqlsql

I have 2 tables: sets and groups. Both are joined using a 3rd table set_has_groups.
I would like to get sets that have ALL groups that I specify

One way of doing it would be

SELECT column1, column2 FROM sets WHERE 
id IN(SELECT set_id FROM set_has_group WHERE group_id = 1)
AND id IN(SELECT set_id FROM set_has_group WHERE group_id = 2)
AND id IN(SELECT set_id FROM set_has_group WHERE group_id = 3)

obviously this is not the most beautiful solution

I've also tried this:

SELECT column1, column2 FROM sets WHERE 
id IN(SELECT set_id FROM set_has_group WHERE group_id IN(1,2,3) GROUP BY group_id
HAVING COUNT(*) = 3

This looks prettier but the problem is that it takes forever to execute.
While the first query runs in like 200ms the 2nd one takes more than 1 minute.

Any idea why that is?

===UPDATE:
I've played with this some more and I modified the 2nd query like this

SELECT columns FROM `set` WHERE id IN(
   select set_id FROM
      (
         SELECT set_id FROM set_has_group 
         WHERE group_id IN(1,2,3)
         GROUP BY set_id HAVING COUNT(*) = 3
      ) as temp         
)

that is really fast
It's the same as the 2nd query before just that I wrap it in another temporary table
Pretty strange

Best Solution

I am suspecting a small mistyping in the second query.

Really, I am not sure. Probably, the second query is executed via full table scan. At the same time the first one "IN" is really transformed into "EXISTS". So, you can try to use "exists". For example:

...
where 3 = (select count(*) from set_has_group 
    where group_id in (1, 2, 3) and set_id = id
    group by set_id)