Re: [myexperiment-hackers] Suggestions for a new event-logging system

[Danius Michaelides]

On Thu, 24 Jul 2008, Sergejs Aleksejevs wrote:

> Hello,
>
> As discussed yesterday, I'm sending my suggestions for improving performance 
> issues with current event-moniroting system.

Looking good - couple of questions below..

> -- Existing problem --
>
> The current situation with the "home" page is as follows - two different 
> activities take place to display all the information about "news" on 
> myExperiment (the same thing as in (2) also happens to display content in 
> "news" on user's profile page) :
>
> 1) Latest announcements, groups, tags, reviews, comments, new users and 
> updated items are retrieved. For this to be done simple queries (SELECT * 
> FROM <table> ORDER BY 'created_at' / 'updated_at' LIMIT x) are issued to each 
> of the involved tables in the DB. However, having many of these definitely 
> has a negative impact on performance.
>
> 2) 'My news' section is populated with data, originating from querying every 
> single entry in different collections of resources (users, contributions, 
> friends of the user, etc) and testing whether it is relevant for being shown 
> to current user or not.
>
>
> -- Solution --
>
> My suggestion to improve performance would be to create a new table (for 
> example, called 'Events'), which would have the following attributes (please 
> also see the attached sketch of this table for better picture of potential 
> values to be stored):
>
> - ID : primary key
> - event_type : String; (type of event, like 'new', 'update', 'membership', 
> 'friendship')
> - action_description : String; (clarification of action that happened - for 
> instance, 'join', 'leave' for memberships)
> - user_id : INT;   (ID of the user who initiated the action)
> - object_id : INT; (ID of the 'contribution' over which the action was done; 
> also - ID of a network that was joined /
>                            left or ID of a user, who has become friend of 
> the current - user_id - user)
> - timestamp : datetime; (when the event has happened: required for sorting 
> and also for ability to remove "old"
>                                   entries from this table)
> - note : String;   (not sure about this field yet, but seems logical to have 
> some space to include textual description)
>
>
>
> The idea of having this table is:
>
> - each time a relevant event will happen, an entry in 'Events' table will be 
> created to reflect the status update
> - a single table (with a relatively small number of entries) will have to be 
> queried to produce all content in the "home" page - both "latest" uploads and 
> the news;
> - "too old" entries from the table (judging from the timestamp) can be 
> deleted on table updates, or when a particular type of query over the table 
> is executed (maybe, when "news" are generated).

I can see how this solves problem 1, but not whether it helps with problem 2.
I think 2 is quite hard to do quickly?

> In the news section there is a fixed range of patterns (all of which can 
> easily be generated from data stored in the Events table presented above):
> - X joined the group A
> - X and Y became friends
> - X created group A
> - X joined myExperiment
> - X created a File / Workflow / Pack, etc.
> - X created a new post on Blog
> - X edited the workflow
> - X selected a new avatar

If the news items are all of this form, we could just have (borrowing from
rdf):
                  <subject> <predicate> <object>
where subject and object are type and id. So the complete table would be
id, timestamp, subject_type, subject_id, predicate, object_type, object_id, note

I think whats confused me about your table is that the action_description
column has both types of things and also actions in it.

> However, there is one type of 'news' pattern which I don't understand:
> - X created the File / Workflow / etc for Y
> Therefore, I'm not sure if there are no additional details that need to be 
> included into events table for ability to generate this type of "news" as 
> well?

Would we still generage Latest Groups|Tags|Reviews|Comments in the usual
way?

How about elsewhere, for example, the news on a group page? I notice that the
news tab only seems to show membership and not, for example, when something
is shared with the group - not sure why, maybe because its hard to find out
when it happened - I think your solution opens up more types of news items.

Danius