Re-writing query to avoid WHERE IN with a subquery

Question

I have the following query which returns the correct results but I am aware of the overheads associated with subqueries in a WHERE IN clause.

SELECT event_start_date 
  FROM events
       JOIN joins 
            ON event_id = join_event_id
       JOIN groups 
            ON join_type_id IN (SELECT group_id 
                                  FROM view_groups 
                                 WHERE user_id = 3)
 WHERE user_id = 3
       AND event_start_date >= now()
GROUP BY 
       event_start_date;

Can anyone help out with rewriting this query to avoid the subquery and/or the WHERE IN clause.

Answer 1

MySQL does optimize uncorrelated subqueries (which you have in your example):

MySQL executes uncorrelated subqueries only once. Use EXPLAIN to make sure that a given subquery really is uncorrelated.

If your subquery returns only one result, be sure to use equal instead:

For uncorrelated subqueries that always return one row, IN is always slower than =.

Regardless, this is how you rewrite your query as a JOIN:

SELECT events.event_start_date 
  FROM events
       JOIN joins 
            ON events.event_id = joins.join_event_id
       JOIN view_groups
            ON view_groups.user_id = 3
       JOIN groups 
            ON groups.join_type_id = view_groups.group_id
 WHERE events.user_id = 3
       AND events.event_start_date >= NOW()
 GROUP BY 
       events.event_start_date;