[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[myexperiment-hackers] Suggestions for a new event-logging system
From: |
Sergejs Aleksejevs |
Subject: |
[myexperiment-hackers] Suggestions for a new event-logging system |
Date: |
Thu, 24 Jul 2008 10:59:54 +0100 |
User-agent: |
Thunderbird 2.0.0.16 (Windows/20080708) |
Hello,
As discussed yesterday, I'm sending my suggestions for improving
performance issues with current event-moniroting system.
-- 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).
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
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?
Thanks,
Sergey
- [myexperiment-hackers] Suggestions for a new event-logging system,
Sergejs Aleksejevs <=