Mysql查询: fastest way to find users followed by or following another user

Question

我有下表:

关系

id、follower_id、followee_id、状态

用户

id、姓名、电子邮件

我想查找正在关注或被特定用户关注的所有用户。

这是我到目前为止所拥有的，但速度非常慢:

SELECT DISTINCT
    `users`.*
FROM
    `users`
        INNER JOIN
    `relationships` ON ((`users`.`id` = `relationships`.`follower_id`
        AND `relationships`.`followee_id` = 1)
        OR (`users`.`id` = `relationships`.`followee_id`
        AND `relationships`.`follower_id` = 1))
WHERE
    `relationships`.`status` = 'following'
ORDER BY `users`.`id`

我所说的慢是什么意思

我有一个用户，大约有 600 个关注者和 600 个关注者，此查询运行大约需要 5 秒，对于这些数字来说，这似乎非常慢!

explain 方法显示以下内容:

+----+-------------+---------------+------+-----------------------------------------------------------------------+------+---------+------+------+------------------------------------------------+
| id | select_type | table         | type | possible_keys                                                         | key  | key_len | ref  | rows | Extra                                          |
+----+-------------+---------------+------+-----------------------------------------------------------------------+------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | relationships | ALL  | index_relationships_on_followed_id,index_relationships_on_follower_id | NULL | NULL    | NULL |  727 | Using where; Using temporary; Using filesort   |
|  1 | SIMPLE      | users         | ALL  | PRIMARY                                                               | NULL | NULL    | NULL |  767 | Range checked for each record (index map: 0x1) |
+----+-------------+---------------+------+-----------------------------------------------------------------------+------+---------+------+------+------------------------------------------------+

Answer 1

尝试使用union将其分成两个查询:

SELECT u.*
FROM `users` u INNER JOIN
     `relationships` r
      ON u.`id` = r.`follower_id` AND r.`followee_id` = 1
WHERE `r.`status` = 'following'
UNION
SELECT u.*
FROM `users` u INNER JOIN
     `relationships` r
      ON u.`id` = r.`followee_id` AND r.`follower_id` = 1
WHERE `r.`status` = 'following'
ORDER BY id;

这可能是更复杂的查询具有更好性能的情况。这些查询还将受益于索引:relationships(status, follower_id, followee_id) 和 relationships(status, followee_id, follower_id)。