Need help optimize SQL query

Question

I want to see all users which following me or another user.

I have got used 3 tables

users

id
username

user_follow

id
user_id
follow_id
date

images

id
user_id
image

I tried to get username with last image uploaded and also I need my and followers status to see if I already follow them or no.

My query is written on Mysql :

SELECT 
          u.id AS user_id,
          u.username,
          i.image,
          t.id AS STATUS 
FROM
          users AS u 
          INNER JOIN (user_follow AS f) 
                    ON (u.id = f.user_id) 
          LEFT OUTER JOIN images i 
                    ON i.id = 
                    (SELECT 
                              b.id 
                    FROM
                              images AS b 
                    WHERE f.user_id = b.user_id 
                    ORDER BY b.id DESC 
                    LIMIT 1) 
          LEFT OUTER JOIN user_follow t 
                    ON t.id = 
                    (SELECT 
                              m.id 
                    FROM
                              user_follow m 
                    WHERE user_id = 3 
                              AND follow_id = u.id) 
WHERE f.follow_id = 7 
          AND f.user_id NOT IN (7, 3) 
GROUP BY f.user_id

My user id - 3; I watch users who are following user with id - 7

And output is :

Array
(
    [0] => Array
        (
            [user_id] => 6
            [username] => 6666
            [image] => flw3utn9igiqh7dtt2o61ydf8_174.jpeg
            [status] => 226
        )

)

I think that my query is too heigh and I do not know how to simple check status and output 1 or 0 . Now I get a follow line id if "follow" and empty if no. Also I will be happy if you show me how to optimaize my query.

To summarize the problem, You want to get image and followers of a user. And also want to check if you are following your follower. Is this correct? Whats the usage of date col in user_follow table — ejb_guy
– ejb_guy, Commented May 24, 2012 at 7:55
yes, I want to get users followers with their username and image also I need some variable to check if "I" already follow or no. My query is working but I think that it is too heavy and maybe be someone can give an idea how to do or how to optimaize — Viktors
– Viktors, Commented May 24, 2012 at 8:32
Your UserId is 3, UserId 7 Follows you and userid 6 follows 7. For given input 3, you want Image for id 3, Follower of 3 that is 7, image for id 7 and flag to check if you (3) follows 7 or not. COrrect? Why id 6 in you output? — ejb_guy
– ejb_guy, Commented May 24, 2012 at 8:57
user with id -> 6 follow user id -> 7 . When I come with my user_id -> 3 to profile page user id -> 7 and try to see all people who follow him or her. For example array give me a users list and in this (follow him) list I do not need to see themself (if I also follow user_id -> 7) and I need to check if I have already this followers or no (this boolean need me to make follow or unfollow button) — Viktors
– Viktors, Commented May 24, 2012 at 10:59
Is there a chanse to do this query a bit smaller or this what I have now is the best — Viktors
– Viktors, Commented May 24, 2012 at 11:00

ejb_guy · Accepted Answer · 2012-05-24 13:06:52Z

1

UVP - user who is viewing the page. FBV - User whose profile\list of follower is getting viewed. Last col of below query will give 0 if UVP is not following FBV else 1. It will be difficult to optimize the query 1) You want the row data in col (transpose){ first LOJ} 2) You have 2 tables where data might or might not be there {next 2 LOJ}

      select *,(case when (uf3.id is not null) then 'follow' else null end) aa 
  from users u  
    left outer join 
                (Select * from 
                 user_follow 
                 where user_id = <FVP>
                 and follow_id =<UVP>) uf3 on uf3.user_id = u.id  
    left outer join 
            (select * from images where user_id = <FBV> ORDER BY id DESC 
                LIMIT 1) i
     on i.user_id = u.id 
    left outer join 
                (Select * 
                 from user_follow 
                 where user_id =<FVP>  
                 and follow_id !=<UVP>)  uf  on uf.user_id = u.id  
    where u.id =<FBV>;

answered May 24, 2012 at 13:06

ejb_guy

1,1256 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Viktors Over a year ago

coming home I will try. From you description "Last col of below query will give 0 if UVP is not following FBV else 1" As I understand with this query I am able only view followers of a user which I follow. In example I need follow 7 to see who follow him ? My idea was that I can see every profile do not depending follow I to user or no

ejb_guy Over a year ago

nope. you will get the list of all the users who follow fbv and last col of each row will indicate if ubv follows fbv. If you are fine with this info (whether UBV follows FBV) than first outer join can be removed and a Union can be done to get this data.

Viktors Over a year ago

I tested, script works faster but it do not show the result above. for example user_id ->7 follow user_id -> 6 and me (3) so When I watch user_id ->7 profile it must return 1 row with user_id -> 6. Now it return me [user_id] => 7, [username] => 8888, . bug is that I look user_id -> 7 and it show me user_id -> 7

ejb_guy Over a year ago

what values u used for fbv(7) and uvp(3) (can we chat...Will be able to help better that way)

Collectives™ on Stack Overflow

Need help optimize SQL query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related