Mysql 5.7慢CASE WHEN THEN查询

Question

我正在使用以下查询获取 friend 关系:

SELECT F.friend_one, F.friend_two, F.requested_id, F.status
FROM users U, friends F
WHERE
CASE
WHEN F.friend_one = 2
THEN F.friend_two = U.id
WHEN F.friend_two= 2 
THEN F.friend_one= U.id
END
AND F.status = 1

( http://www.9lessons.info/2014/03/facebook-style-friend-request-system.html )

但是，从具有 7500 条记录的 friends 表中选择结果需要 1.5 秒。有没有办法解决这个问题？我不是sql专家。解释表明我正在执行我认为造成问题的所有选择。

Answer 1

使用 union 可能会更好或 union all比使用 or或 case在on子句:

SELECT F.friend_one, F.friend_two, F.requested_id, F.status
FROM users U JOIN
     friends F
     ON F.status = 1 AND F.friend_one = 2 AND F.friend_two = U.id
UNION ALL
SELECT F.friend_one, F.friend_two, F.requested_id, F.status
FROM users U JOIN
     friends F
     ON F.status = 1 AND F.friend_two = 2 AND F.friend_one= U.id;

您可能还需要索引。我会推荐 friends(status, friend_two, friend_one)和 friends(status, friend_one, friend_two) .