Database Optimization

[Acute]

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.

[griswolf]

Not sure I see the need for the first query. If the friends table is asymmetric and looks like

friends {
  i_am foreign key references user_info,
  my_friend foreign key references user_info
}

And you just SELECT p.message from posts p join friends f on f.my_friend = p.author where f.i_am = $my_id order by p.when_posted LIMIT $max_posts_to_show (this is completely untested)

You could extend this to join with the user table to get the author info that you want to display. If the appropriate columns are key / indexed, this should be much more than sufficiently quick until you get a bazillion messages at which point you want to partition the tables to deal only with recent ones, for some definition of 'recent'

[Acute]

Thanks a lot)
Do you mind if I continue this issue? :]
You see the $my_id will always change because different users will login, so I'm not able to create a VIEW of this select. What do you think about this? Is it necessary to create a VIEW?

[griswolf]

It is never necessary to create a VIEW. Sometimes convenient. Your mileage may vary...

[lobojohnson]

This is for the first time , i have heard that Database optimization is being used so popularly in IT companies. So something new , i learned from here

[Acute]

@griswolf:

"You could extend this to join with the user table to get the author info that you want to display."

how to correctly JOIN three tables?

[griswolf]

The short answer is 'just like with two'. The medium answer is RTFM . And the long answer is 'it depends on exactly what you need to have happen' (in other words: RTFM, and probably, experiment)

P.S. Once a thread is marked 'solved' it is generally considered bad form to add to it. I've done so because you are the OP here, but next time you might want to think before you hit the 'solved' button.