Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    15

    Exclamation db optimization, plz help

    Hi everyone,
    I'm building a microblogging system like twitter and I need help in optimizing the database structure.
    I have tables "user_info", "posts" and "friends". Each time when user logs in, lets say user X, all latest posts of friends of X should be displayed.
    So it means that when user logs in I need to:

    1)Get all friends of X with query#1

    2)Get all latest posts of each friend with query#2 (i make a long query using php like "...from posts where author = A or author = B or author = C or...or author = Z", where A, B, C, D... are friends that I get with query#1 in step 1)

    3)In "posts" table in 'author' column user_id is saved, so for each post I need also to Get information about author like name, sex from "user_info" table using user_id from posts.

    To optimize it a little bit I use JOIN to make steps 2 and 3 with ONE request instead of two.
    To optimize it more I could use VIEWs, but the point is that the WHERE clause is always different, because different users log in -> "SELECT ..... FROM...JOIN...ON () WHERE friend1 = $currentUser".

    So question #1: are my database strucure and idea right?
    question #2: is it possible to use VIEWs if WHERE clause is changing each time?

    Thnx in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    #1. so far so good

    #2. sure it is, just omit the WHERE clause from the view
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •