mysql - 有关如何优化此 MySQL 查询以更快运行的建议

Question

SELECT * 
FROM   members 
WHERE  memberid IN (SELECT follows.followingid 
                    FROM   follows 
                    WHERE  follows.memberid = '$memberid' 
                           AND follows.followingid NOT IN (SELECT memberid 
                                                           FROM   userblock)) 
       AND memberid NOT IN (SELECT blockmemberid 
                            FROM   userblock 
                            WHERE  memberid = '$memberid')

上面的查询在 MySQL 中执行需要将近 4 秒，我想知道是否有人对我如何改进/优化它以实现更快的执行时间有任何建议？

Answer 1

用联接替换 in 子句。我认为以下内容捕获了逻辑。请注意，not in 变成了一个 left join，在 where 子句中找到一个不匹配的条件

SELECT m.* 
FROM members m
     follow f
     on m.memberid = f.followingid and 
        f.memberid = $memberid left join
     userblock ubf
     on follows.followingid = ubf.memberid left join
     userblock ub
     on m.memberid = ub.blockmemberid and
        ub.memberid = '$memberid'
where ub.blockmemberid is null and
      ubf.memberid is null;