I am about to code a messaging system where users can write messages to other users. User can create custom inbox folders for sorting the messages they receive, however, every user has 2 main inboxes:
a) with all messages they receive
b) all messages they receive filtered by users they are friends with
For all mailboxes the total/unread numbers inside shall be displayed.
This leads me to the need of the following tables:
- users (id, username,...)
- friends (user_id, friend_id)
- messages (id, sender_id, receiver_id, read_status, mailbox_id, ...)
- mailbox (id, name, owner_id, ...)
My question regarding the perfomance (thinking of a situation where there are many messages and users):
Is is better to calculate the total/unread values for each mailbox for each page view?
Select count(.) as total,
.... ,
Group By mailbox_id, friend_status, read_status
or
Is it better to store these information in the table mailbox
in extra fields like total_msg, total_unread, total_friends, total_friends_unread
- and update these values upon each event (new message, read message, new friends, friends break up, etc...)?
Would there be a 3rd approach to be considered?
Thanks for help!