I'm newbie in database optimization and I have some questions about it:
First of all, what is better for table that contains messages like posts or tweets or comments: author_id | message or author_fullname | message ? I know that first variant is better, but I don't know why it is better. While displaying the message to the user, you will show the authors name, not id, right? It means you'll need one more request to get the information about author like name, age, etc. So isn't it better to keep the information about author in messages table to avoid one more request?
How size of a table affects on speed? I thought that if table is well-sorted, retrieving data will be ok despite the size of table. Is it wrong belief?
The only reason to use name or subset of the name is if it is useful. There are times for ease of entry to use shortened names "Oil on canvas" OOC or "Oil on board" OOB. The difference boils down to recognition.
So my situation is:
I want to display microblogs like in twitters. To be more precise authors name, age, sex, posting date and the message of all friends will be displayed to the user.
So, my question is how my database should be structured? author_id | message and then retrieve one more time the user information using the id,
or use author_id | author_name | author_sex | author_age | message to avoid extra querying?
I have read about JOIN and that is really what I need. However, I've heard that JOINs slow down everything(do they?) and it would be good to use VIEWs. But in my situation the WHERE clause is always different, so how to use VIEW if my WHERE clause is always different??? (in my site each time when registered user logs in, his/her friends activity should be displayed(like in fb or twitter), so the WHERE clause is like "WHERE friend1 = $currentUser")