MySQL – Efficient Way to Query for Count Totals

Ok, first off I think you are almost there. From looking at the schema and data one thing I have noticed is that you seem to have overlooked the fact that a friendship is bi-directional. So when you create a friend entry from a request you also need to create one in the other direction as well:

INSERT INTO `default_friend` (`friend_id`, `user_id`, `is_suscriber`, `privacy`, `created_at`, `friend_list_id`, `approved`) 
VALUES (1, 2, 1, 0, '2012-08-13 18:16:11', 0, 1);

After you have done that your query should be more like the result you are after. Running this query:

select distinct u.id as `user_id`, u.username, f.id as `friend_id`, f.username as friend, s.*
from default_users as u
left join default_friend as df on df.user_id = u.id
left join default_users as f on f.id = df.friend_id
left join default_status as s on s.user_id = u.id
left join default_comment as c on c.status_id = s.status_id
order by s.status_id;

returns the following result set:

user_id username    friend_id   friend  status_id   message created_at  privacy user_id is_reply    device  
1   admin   2   demo    1   dasdasdasdasdasd    2012-08-13 19:45:37 NULL    1   0   
2   demo    1   admin   2   dasdasdasdasdasd    2012-08-13 19:46:03 NULL    2   0   
1   admin   2   demo    3   dasdsad344hbvnbnhjhgjhjghjhj    2012-08-13 21:54:53 NULL    1   0

Is this anywhere near what you are looking for?

The "Best Way" is probably to do it the former way, calculating the accurate count of messages and friends, etc is going to be easier to manage on the application side. Performance will obviously depend on how many messages and friends, some variables on the database server, and indexes.

A third solution would be to use a caching layer (like memcached) to store the values that aren't going to change that much. Memcache can increment and decrement, which will work perfectly for updating your unread counts. Message counts only get updated when new messages are received, friend counts are only updated when new friends are added (and possibly with the added condition of that friend having to send a message). My point is, here, that the values here don't update that frequently, so memcached is a pretty perfect option for a solution here.

That being said, stressing about the performance of that schema early on before you have factual data on how it's being used and how it's performing definitely falls into the camp of "over optimizing." Implementing approach #2 or #3 is a trivial add once you have the system in place. Build it first, do what you believe is best, don't over optimize or over engineer. Once it's online, monitor performance, and make fixes as you identify bottlenecks.