0

I have three tables. One table has information on users, such as their name, bio, age etc. The primary key is the Id field alone. Second table shows people users follow (user_following) and the third table is to store relations with people who follow me (user_followers).

I want to find information on those users who have been removed from both the tables.

Users can have large IDs and hence the ID column is varchar with the lenght being max 25 characters as of now.

My query is this:

select * 
FROM followers_info finfo 
LEFT JOIN user_followers uf 
    ON uf.follower_id = finfo.follower_id 
LEFT JOIN user_following fing 
    ON fing.follower_id = finfo.follower_id 
WHERE uf.follower_id IS NULL 
  AND fing.follower_id IS NULL

Attached is the explain results of this query: enter image description here

Is there a way to improve the performance here?

1 Answer 1

0

You can try to rewrite it as:

select * 
FROM followers_info finfo
WHERE NOT EXISTS ( 
    SELECT 1 FROM user_followers uf 
    WHERE uf.follower_id = finfo.follower_id
)
AND NOT EXISTS (
    SELECT 1 FROM user_following fing 
    WHERE fing.follower_id = finfo.follower_id 
);

or:

select * 
FROM followers_info finfo
WHERE NOT EXISTS ( 
    SELECT 1 FROM user_followers uf 
    WHERE uf.follower_id = finfo.follower_id
    UNION ALL
    SELECT 1 FROM user_following fing 
    WHERE fing.follower_id = finfo.follower_id 
);

Generally no better nor worse, but worth a try

1
  • Thanks, will try this and let you know.
    – dinwal
    Aug 26, 2019 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.