CODEWITHSUNDEEP
phpmysql
JasonDavis
JasonDavis

Reputation: 48933

How to make my mysql queries for a friend feed on a large DB?

Here is my mysql table scheme;

  1. status table // has statusID, userID,date, subject, date
  2. comment table // holds commentID, userID who posted, date, and statusID that it belongs too
  3. user table //holds userID username and user photo URL

With that table scheme above, I need to do this;

  1. Fast with least amount of DB queries
  2. Show all status entries on a page that are published from a user in my friend list (could be up to 5,000 friends)
  3. show all comments for each status entry under the appropriate blog
  4. show a username/photo URL for every status entry post
  5. show a username/photo URL for all comment posters next to there comment

The result I am after is similar to myspace or facebook where it will show all post/actions or whatever from just your FRIENDS only.

You could even compare THIS page on stack overflow to what I am trying to accomplish, consider my post as a status post and all answers on this page would be status post, then under all answers on this page, it shows comments under each one and they all have user info, is there a better way to accomplish this without so many joins and stuff?

Question 1
Is there anyway better to accomplish what I need? This is not really fast enough when there are millions of rows that it searches through even with indexes, what are my options?

Question 2
Is it possible to modify this to just show the first X ammount of comments on each status post? And if so, would that speed it up since it wouldn't have to search through as many comments?

Below the friend List is already in the query, the reason for that is I plan to get the friend list into an Array and store it in memcache or APC cache so it will be 1 less query

Here is my query 

SELECT s.statusid, s.userid, s.statustype, s.subject, 
     s.datetime, c.commentid, c.statusid, c.userid, 
     c.comment, c.datetime, su.disp_name, 
     su.pic_url, cu.disp_name, cu.pic_url
FROM teststatus AS s
   LEFT JOIN teststatuscomments AS c 
       ON s.statusid = c.statusid
   LEFT JOIN friend_reg_user AS su
       ON su.auto_id = s.userid
   LEFT JOIN friend_reg_user AS cu
       ON cu.auto_id = c.userid
WHERE s.userid =1 OR s.userid
       IN ( 2, 3, 4, 5, 6, 7, 8, 9, 
            10, 11, 12, 13, 14, 15, // Remember this list of friend ID's
            16, 17, 18, 19, 20 )  //can be from any ammount of ID's  up to 5,000
ORDER BY s.statusid

PS) I will start a bounty on this as soon as it lets me

Upvotes: 1

Views: 863

Answers (1)

Charles Bretana
Charles Bretana

Reputation: 146499

Your join is based on the same field being equal to two different values, so it cannot ever be satisfied

 ON fru.auto_id = s.userid
          AND fru.auto_id = c.userid

You need TWO joins to the user table

SELECT s.statusid, s.userid, s.statustype, s.subject, 
     s.datetime, c.commentid, c.statusid, c.userid, 
     c.comment, c.datetime, su.disp_name, 
     su.pic_url, cu.disp_name, cu.pic_url
FROM teststatus AS s
   LEFT JOIN teststatuscomments AS c 
       ON s.statusid = c.statusid
   LEFT JOIN friend_reg_user AS su
       ON su.auto_id = s.userid
   LEFT JOIN friend_reg_user AS cu
       ON cu.auto_id = c.userid   -- EDIT 
WHERE s.userid =1OR s.userid
       IN ( 2, 3, 4, 5, 6, 7, 8, 9, 
            10, 11, 12, 13, 14, 15, 
            16, 17, 18, 19, 20 ) 
ORDER BY s.statusid

Upvotes: 3

Related Questions