What table structure would you suggest for this?

Hello,
I've been asked to code a feature of "whats happenning" on an existing site of over 100,000 members and growing.
People will be able to write a line ( status ) and post it. Then their friends etc will be able to see this status update on their pages. Something like twitter.

Right now I'm thinking that with 100,000 members, this database will grow huge and selecting data can become slow.
What kind of table structure can I use so it doesn't get too slow? (mysql)

I'm thinking something like this for now:

ID int, userName varchar, status varchar, postTime timestamp , INDEX( userName )

Now if user1 opens their page, and has user2, user3 as friends, then status of both user2, user3 will be shown.

select status from table where userName='user2' and userName='user3' ;

Instead of using AND, I can use IN to give the query a list of users to match with.

However, I think that in time the structure of table might cause problems in terms of speed.

What are your thoughts? And please suggest a better structure if possible.

Thanks

 

I would recommend using IDs for querying of statuses.

SELECT * FROM statuses WHERE user_id IN (SELECT user1_id FROM friends WHERE user2_id = {me})

Code (markup):

Not sure how your existing tables are setup, but this is called "taxonomy".

 

You can do this in this way

if there is a user table already then like structure (user_id as integer,username as varchar,address as varchar etc...).
Then,
make another table for friends as friendstable structure like(frnd_id as int,frnd_name as varchar,user_id as integer... etc other details of friends).
also make another table for status as statustable structure like (status_id as int,post_status as varchar,user_id as integer...etc ther details of status post)

now write query in this way:
select * from statustable join usertable on usertable.userid=statustable.user_id where statustable.user_id in (select frnd_id from friendstable) and usertable.userid='$currentuserid'


where currentuserid is the id of user who is login right now