fast_one.sql

select student_id
from (
  select gs.student_id, g.id as group_id
  from groups_students gs
  inner join groups g on g.id = gs.group_id
  inner join group_types gt on gt.id = g.group_type_id
  where gt.permission_group = 1
) s
left outer join (
  select g.id as group_id
  from groups_users gu
  inner join groups g on gu.group_id = g.id
  inner join group_types gt
  on gt.id = g.group_type_id
  where gu.user_id = 137 and gt.permission_group = 1
) u
on s.group_id = u.group_id
group by s.student_id
having count(*) = count(u.group_id);
-- 31 rows in set (0.01 sec)

EXPLAIN:

+----+-------------+------------+--------+--------------------------------------------------------------+--------------------------------------------------+---------+---------------------------+------+---------------------------------+
| id | select_type | table      | type   | possible_keys                                                | key                                              | key_len | ref                       | rows | Extra                           |
+----+-------------+------------+--------+--------------------------------------------------------------+--------------------------------------------------+---------+---------------------------+------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL                                                         | NULL                                             | NULL    | NULL                      | 1145 | Using temporary; Using filesort |
|  1 | PRIMARY     | <derived3> | ALL    | NULL                                                         | NULL                                             | NULL    | NULL                      |    1 |                                 |
|  3 | DERIVED     | gu         | ref    | index_groups_users_on_user_id,index_groups_users_on_group_id | index_groups_users_on_user_id                    | 5       |                           |    3 | Using where                     |
|  3 | DERIVED     | gt         | ALL    | PRIMARY                                                      | NULL                                             | NULL    | NULL                      |    3 | Using where; Using join buffer  |
|  3 | DERIVED     | g          | eq_ref | PRIMARY,group_type_id                                        | PRIMARY                                          | 4       | my_db.gu.group_id         |    1 | Using where                     |
|  2 | DERIVED     | gt         | ALL    | PRIMARY                                                      | NULL                                             | NULL    | NULL                      |    3 | Using where                     |
|  2 | DERIVED     | g          | ref    | PRIMARY,group_type_id                                        | group_type_id                                    | 5       | my_db.gt.id               |   98 | Using where; Using index        |
|  2 | DERIVED     | gs         | ref    | index_groups_students_on_group_id_and_student_id             | index_groups_students_on_group_id_and_student_id | 4       | my_db.g.id                |    9 | Using index                     |
+----+-------------+------------+--------+--------------------------------------------------------------+--------------------------------------------------+---------+---------------------------+------+---------------------------------+

slow_one.sql

select students.* from students
where students.id IN (
  select student_id
  from (
    select gs.student_id, g.id as group_id
    from groups_students gs
    inner join groups g on g.id = gs.group_id
    inner join group_types gt on gt.id = g.group_type_id
    where gt.permission_group = 1
  ) s
  left outer join (
    select g.id as group_id
    from groups_users gu
    inner join groups g on gu.group_id = g.id
    inner join group_types gt
    on gt.id = g.group_type_id
    where gu.user_id = 137 and gt.permission_group = 1
  ) u
  on s.group_id = u.group_id
  group by s.student_id
  having count(*) = count(u.group_id)
);
-- 31 rows in set (1.12 sec)

EXPLAIN:

+----+--------------------+------------+--------+--------------------------------------------------------------+--------------------------------------------------+---------+---------------------------+------+---------------------------------+
| id | select_type        | table      | type   | possible_keys                                                | key                                              | key_len | ref                       | rows | Extra                           |
+----+--------------------+------------+--------+--------------------------------------------------------------+--------------------------------------------------+---------+---------------------------+------+---------------------------------+
|  1 | PRIMARY            | students   | ALL    | NULL                                                         | NULL                                             | NULL    | NULL                      | 1133 | Using where                     |
|  2 | DEPENDENT SUBQUERY | <derived3> | ALL    | NULL                                                         | NULL                                             | NULL    | NULL                      | 1145 | Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | <derived4> | ALL    | NULL                                                         | NULL                                             | NULL    | NULL                      |    1 |                                 |
|  4 | DERIVED            | gu         | ref    | index_groups_users_on_user_id,index_groups_users_on_group_id | index_groups_users_on_user_id                    | 5       |                           |    3 | Using where                     |
|  4 | DERIVED            | gt         | ALL    | PRIMARY                                                      | NULL                                             | NULL    | NULL                      |    3 | Using where; Using join buffer  |
|  4 | DERIVED            | g          | eq_ref | PRIMARY,group_type_id                                        | PRIMARY                                          | 4       | my_db.gu.group_id         |    1 | Using where                     |
|  3 | DERIVED            | gt         | ALL    | PRIMARY                                                      | NULL                                             | NULL    | NULL                      |    3 | Using where                     |
|  3 | DERIVED            | g          | ref    | PRIMARY,group_type_id                                        | group_type_id                                    | 5       | my_db.gt.id               |   98 | Using where; Using index        |
|  3 | DERIVED            | gs         | ref    | index_groups_students_on_group_id_and_student_id             | index_groups_students_on_group_id_and_student_id | 4       | my_db.g.id                |    9 | Using index                     |
+----+--------------------+------------+--------+--------------------------------------------------------------+--------------------------------------------------+---------+---------------------------+------+---------------------------------+