How to optimize SQL query more?

Question

First at all i am nood into SQL thing, Now i am working on a class project where I have some tables like

Table user

user_id  |  username | name 
   1     |    nihan  |  Nihan Dip
   2     |     dip   |  Meaw ghew
  more   |  more     | more

Table Friend

you    | friend_id  
 1     |    2
 1     |    27
 2     |    9
 more  |   more

Table Follow

user_id   |  follows
  1       |   99
  7       |   34

Table post

post_id   |  user_id  | type  |  content  | post_time
  1       |   1       |  text | loren toren | timestamp
  2       |   2       | text  | ipsum       | timestamp

Now i want to get post by users friend and who he follows and offcourse his so i made this SQL

SELECT 
    username, name,content, post_time
FROM
    post
        INNER JOIN
    user ON user.user_id = post.user_id
WHERE
    post.user_id IN (SELECT 
            friend_id
        FROM
            friend
        WHERE
            you = 1 

        UNION ALL 

        SELECT 
            follows
        FROM
            follow
        WHERE
            user_id = 1)
        OR post.user_id = 1
ORDER BY post_time DESC
LIMIT 10

this query works just fine. I just wanted to know is there anymore optimization could be done? Then how? Please teach me :)

post.user_id => index , post.post_time => index , user.user_id,username => primary and unque keys , also every user_id are FK to user.user_id — ddiipp
– ddiipp, Commented Sep 14, 2013 at 13:29

John Woo · Accepted Answer · 2013-09-14 13:29:13Z

2

Instead of using IN try it with JOIN add add few more indexes.

SELECT  DISTINCT u.name, u.username,
        p.content, p.post_time
FROM    post p
        INNER JOIN user u
            ON u.user_id = p.user_id
        INNER JOIN
        (
            SELECT  friend_id id
            FROM    friend
            WHERE   you = 1 
            UNION ALL 
            SELECT  follows id
            FROM follow
            WHERE user_id = 1
        ) s ON p.user_id = s.ID
ORDER BY post_time DESC
LIMIT 10

answered Sep 14, 2013 at 13:29

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

2 Comments

ddiipp Over a year ago

Few more indexes in where? any suggestion ?

John Woo Over a year ago

friend.you and follow.user_id since you are searching on those fields.

Collectives™ on Stack Overflow

How to optimize SQL query more?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related