重写查询以使用联接而不是子查询

Question

我有以下查询作为 CRON 作业的一部分运行，其性能非常糟糕。我想重写它，也许将子查询转换为联接，但我不知道如何。

该查询归结为如下内容：

SELECT 
    u.id, gu.level
FROM game_users gu
INNER JOIN users u ON u.id = gu.id
WHERE
    gu.points >= 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 > '2011-08-12 14:56:00'
ORDER BY gu.points DESC
LIMIT 1

解释输出：

+----+--------------------+-------+--------+----------------------------------------+-----------------------------+---------+--------------------------+------+-------------------------------------------+
| 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                               |
+----+--------------------+-------+--------+----------------------------------------+-----------------------------+---------+--------------------------+------+-------------------------------------------+

Answer 1

用 LEFT OUTER JOIN 替换子选择应该返回相同的结果。

SQL语句

SELECT  u.id
        , gu.level
FROM    game_users gu
        INNER JOIN users u ON u.id = gu.id
        LEFT OUTER JOIN group_members gm ON gm.user_id = gu.id
        LEFT OUTER JOIN groups g ON g.id = gm.group_id AND g.active = 1
WHERE   gu.points >= 5 
        AND g.id IS NULL
        AND gu.level BETWEEN 5 AND 6 
        AND gu.ai = 0 
        AND u.last_visit > '2011-08-12 14:56:00'
ORDER BY 
        gu.points DESC
LIMIT 1

Answer 2

尝试这样的事情：

SELECT 
    u.id, gu.level
FROM game_users gu
INNER JOIN users u ON u.id = gu.id
inner join (
    SELECT gm.user_id, COUNT(g.id) as count
    FROM groups g
    INNER JOIN group_members gm ON gm.group_id = g.id
    WHERE g.active = 1
    group by gm.user_id
    having count(g.id) = 0
) a on gu.id = a.user_id
WHERE gu.level BETWEEN 5 AND 6 
    AND gu.ai = 0 
    AND u.last_visit > '2011-08-12 14:56:00'
ORDER BY gu.points DESC
LIMIT 1