title: Rewrite query to use joins instead of a subquery
body: <p>I have the following query that runs as part of a CRON job, which performs very badly. I want to rewrite it, maybe converting the subquery into a join, but I'm not sure how.</p>
  
  <p>The query boils down to something like this:</p>
  
  <pre><code>SELECT 
      u.id, gu.level
  FROM game_users gu
  INNER JOIN users u ON u.id = gu.id
  WHERE
      gu.points &gt;= 5 AND
      (
          SELECT COUNT(g.id)
          FROM groups g
          INNER JOIN group_members gm ON gm.group_id = g.id
          WHERE g.active = 1
          AND gm.user_id = gu.id
      ) = 0 AND
      gu.level BETWEEN 5 AND 6 AND
      gu.ai = 0 AND
      u.last_visit &gt; '2011-08-12 14:56:00'
  ORDER BY gu.points DESC
  LIMIT 1
  </code></pre>
  
  <p>Explain output:</p>
  
  <pre><code>+----+--------------------+-------+--------+----------------------------------------+-----------------------------+---------+--------------------------+------+-------------------------------------------+
  | id | select_type        | table | type   | possible_keys                          | key                         | key_len | ref                      | rows | Extra                                     |
  +----+--------------------+-------+--------+----------------------------------------+-----------------------------+---------+--------------------------+------+-------------------------------------------+
  |  1 | PRIMARY            | gu    | range  | PRIMARY,level_points_ai                | level_points_ai             | 10      | NULL                     | 2016 | Using where; Using index; Using temporary |
  |  1 | PRIMARY            | u     | eq_ref | PRIMARY,last_visit                     | PRIMARY                     | 4       | joomla.gu.id             |    1 | Using where                               |
  |  2 | DEPENDENT SUBQUERY | g     | ref    | PRIMARY,active                         | active                      | 5       | const                    |  549 | Using where; Using index                  |
  |  2 | DEPENDENT SUBQUERY | gm    | eq_ref | group_id_user_id,party_member_to_party | group_id_user_id            | 8       | joomla.g.id,joomla.gu.id |    1 | Using index                               |
  +----+--------------------+-------+--------+----------------------------------------+-----------------------------+---------+--------------------------+------+-------------------------------------------+
  </code></pre>