Complexity in choosing one method over another for fetching data from Mysql

I am working on a project based on PHP, Mysql , Apache.

I have a module called notification which is just like Notification available in FACEBOOK, for this I have 3 ways

either I insert every update to a table, in a count of users who are needed to be notified for these updates i.e. if suppose a category B has some updates and this category B contains 100 users then I will insert 100 rows for the respective user in a say, notification table.

2nd way

I can do is just Insert a particular cate开发者_开发问答gories entry in table and then file a heavy JOIN query on each of these caregory ID to fetch all the records from multiple tables to fetch the records.

3rd Way

I use trigger on every updation so that it can be used for notifying the users.

I believe all three ways are problematic at some point.

Now can any one suggest any better idea or which one of these is better option?

I am concerned about the Performance of the site as it will have bulk entries

Thanks

This was a bit trickier then I first thought :)

How is the app / user behavior? When should we do the most of the work? Is it when inserting the data, or when we fetch the data.

I will have to make assumptions on what will be most common operations. I would assume that fetching data will a little more common, due to many users will see notification of unread messages, but only one 'insertion' of a new message?

Therefore I would put the work on insertion of new notices, but still it might end up badly when users starts flocking to your app.

But I think the Model needs to be set before any optimizations, it's more important. Optimizations can later be managed by using denormalisation, scheduling etc. I wouldn't really touch triggers, they are a bit funky IMHO.

I will also make another approach.

User writes a new message:

• Insert message

• Update user_category (has many) category as updated at that time

  UPDATE user_category SET last_changed = 'NOW()' where category_id = ?;

How to find users unread messages:

• Select categories that has been updated since last time user looked at it (dirty?).
• Select all messages from those categories that isn't in the 'user_message_noticed' (see below).

On user has read message

• Insert a row into a *user_message_noticed* schema that couples messages and users. The category id is there so we can do a quicker above without a extra join.

On all messages read from that category - Update *user_category™ (has many many) with a date when user had read all the messages.

But sometimes, you can't really get away from doing the actually work.

Trigger support in MySQL is shoddy at best, so I would not push in that direction although it could have been a good way to do it.

The lightest way to do it will be the following : for every log in / log out, insert / delete user from "online users".id, then have the front-end (which has jack-shit to do anyway) ask now and then to know which of the id's he knows are in the table.

Joins, apart from being relatively not very smart in mysql, will be slow on big tables even with indexes.

Triggers, just the same and then, what would you ask the trigger to do ?? push to all that this person has logged in ? push to those interested ? this means yet another join, i.e. performance hit.

Notification table ? totally huge and slow for no reason (i.e. 100 inserts instead of one where query .. meh).