Views VS Tables For "facebook" like feed

Question

Hey Guys,

I am currently designing a dashboard feed that functions similar to the facebook feed. The feed will contain the following:

• Notify user of new users
• Show referrals sent to users, referrals received
• News from the administrator
• Up comming events.

Each of the above is stored in it's own table but on the feeds page, they should be all mashed together. That's fine, so the first thing that comes to my mind is a table. With a table, it is easier to debug, will allow me to keep historic data, and it is easy to change if I need to use if for other purposes, for example, if we give the user the ability to make notifications sticky.

So I was going over my solution with my manager, and he said that it might be better to use a view because a SELECT takes up a lot less resources than the typical delete and inserts. I do agree with him, because at the beginning of each page load, I have to do house cleaning on the table, delete unused records, remove items that are no longer valid, and repopulate with updated data. Now, if they keep hitting refresh, there will be no work to be done therefore the performance hit isn't bad, but if 1000 users hit the site at the same time, there maybe a huge performance hit if that one table is populating for all these users.

I guess my only concern about using a view is that I'm a sucker for historical data and the fact that later on if the user wants to customize the view, it will be easier; but at this point there is no mention of that, so performance is the only issue at this time.

Here is what needs to be done for the notification:

1. Show up to date information from multiple tables
2. these items have to be mixed in and ordered by date entered system
3. Some of these will be sticky, and some won't based on what the administrator decides (these will be statically set for now)
4. The ability to remove items from the list
5. Each user will have their own area to show their own items.
6. items that will always be shown for new users. For example, if there is a welcome message that needs to be displayed when the user first logs in
7. if the notifications go over 100, remove all notifications after 100 and never show them even if the notifications go under 100

Here is my two tables that are helping me accomplish this task:

notifications
- id
- user_id (If this is null, this item is viewable by all users)
- table_pk (the primary ID for the table this links to
- table (the table that this row links to)
- sticky (is the item sticky)
- date_added (date added)

notifications_removed
- id
- notification_id
- user_id

Right now I have a refresh function that populates that table, but what my manager was thinking is that we get rid of the notifications table and make that into a view. Would there be benefits to doing this? As mentioned, these items will be refreshed each time, so the performance hit is that my way will have to do table cleaning of the notifications table whereas the view just selects and joins things together. On the other hand, the only down side I see to the view is that I will lose the flexability and history of notifications, which isn't a huge thing right now.

Answer 1

A non-materialized view (MySQL doesn't support materialized views) is no different than a prepared SELECT statement - you can run the two side-by-side and the performance is identical. Simple WHERE clauses (predicates) applied to a view can be pushed into the inner query, but that means no data manipulation (IE: function on a column, aggregate functions, etc).

Replacing the notifications with a view of the data being summarized would alleviate UPDATEs to the tables, making the system more "real time". Do you have a need for the historical data handy? You could restore it from backups (though it'd be messy reconstructing data over numerous backups), or you could add a flag to the data so the deleted data isn't removed from the table - the flag just controls the visibility (but this means holding more data).

Previously

If you provided more detail for the situation in another question, we could help you optimize. Generally, you cache data where possible because database trips are expensive, but it's a balancing act for decide when to refresh that cache.