Suggestions on how I can optimize this MySQL query to work faster

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') 

The executed query takes almost 4 seconds to execute in MySQL, and I want to know if anyone has any suggestions on how I can improve / optimize it to achieve faster execution time?

Answer 1

Replace the in clauses with joins. I think the following captures the logic. Note that not in turns into a left join with a condition in the where clause that detects a mismatch

 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; 

Answer 2

It looks similar, but you have fewer subqueries.

 SELECT * FROM members m WHERE EXIST (SELECT f.followingid FROM follows f WHERE f.memberid = '$memberid' AND f.followingid = m.memberid) AND NOT EXIST (SELECT u.blockmemberid FROM userblock u WHERE (m.memberid = '$memberid' AND u.blockmemberid = m.memberid) OR (u.blockmemberid = m.memberid AND u.memberid = m.memberid) ) 

This is the logic that I turned over, created from your code without looking at tables.

Answer 3

 SELECT m.* FROM members m INNER JOIN follows f ON f.followingid = m.memberid AND f.memberid = '$memberid' LEFT OUTER JOIN userblock ub1 ON f.followingid = ub1.memberid LEFT OUTER JOIN userblock ub2 ON m.memberid = ub2.blockmemberid AND ub2.memberid = '$memberid' WHERE ub1.memberid IS NULL AND ub2.blockmemberid IS NULL