Optimizing SQL JOIN query

Question

I have two database table 'friends' and 'published_post'

`friends` --- contains 650K rows 
--------------------------------------------------------
| fb_id_user(bigint)  |   fb_id_friend(bigint) | fb_username(text) |
---------------------------------------------------------


`published_post` ----- contains 6K rows
-------------------------------------------------------------------------------
| id(primary_key)(int)  |   fb_id(bigint) | post(text) |  post_date(DATE_TIME) |
-------------------------------------------------------------------------------

suppose a user has facebook id '12345'. I want to get all the post(form published_post table) given by his friends(from friends table where fb_id_user = '12345') in descending order of 'post_time'. Any suggestion how this query can be done efficiently would be very helpful.

The code uses backquotes so I removed the SQL Server tag.

Gordon Linoff
– Gordon Linoff

2013-12-30 12:31:27 +00:00
Commented Dec 30, 2013 at 12:31 — Gordon Linoff
– Gordon Linoff, Commented Dec 30, 2013 at 12:31

Muhammad Raheel · Accepted Answer · 2013-12-30 12:36:32Z

3

SELECT
    pp.*
FROM published_post pp
LEFT JOIN friends f ON f.fb_id_user = pp.fb_id
WHERE pp.fb_id_friend = '12345'
ORDER BY f.post_date DESC

answered Dec 30, 2013 at 12:36

Muhammad Raheel

19.9k7 gold badges69 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

Optimizing SQL JOIN query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related