Activity feed with PHP MySQL

Question

I'm in the making of some kind of "activity stream/feed" using PHP and MySQL.

I got one table for the stream which simply just stores the type of the activity and the timestamp. I then need to lookup the actual data for the activity which can be find in various tables (depending on the activity-type). The stream should be able to handle hundreds of users and I therefore need it to perform well. The options I got:

• Get the stream table - loop through in PHP with some kind of switch and then call SQL for the appropriate activity data (for each activity). (Slow)
• Make one big SQL sentence with some joins taking all the activity data in one request. Still doing the switch in PHP but without having to call SQL again. (Is this slow for big tables?)
• Using some kind of views with MySQL - I haven't done this before.
• Using multiple SQL calls to get all activity data and store them in PHP arrays and from there do the switching.

Do I have any other options? What would be the best approach in terms of performance?

Answer 1

You may perform a data-driven JOIN with a single query. Essentially, you would JOIN every needed subtable and then select, by aliasing, the content from the table you needed. Assuming 1 is activity_drink, 2 is activity_eat and 3 is activity_sports and all subtables have the column content, which you'd like to fetch:

SELECT a.`timestamp`,
    CASE a.`activity_type`
        WHEN 1 THEN ad.`content`
        WHEN 2 THEN ae.`content`
        WHEN 3 THEN asp.`content`
    END AS content
FROM activities AS a
LEFT JOIN activity_drink AS ad ON (ad.`activity_id` = a.`activity_id`)
LEFT JOIN activity_eat AS ae ON (ae.`activity_id` = a.`activity_id`)
LEFT JOIN activity_sports AS asp ON (asp.`activity_id` = a.`activity_id`)

This would basically denormalize your tables at select time. You could also convert it to a VIEW for ease of access. It shouldn't be too expensive, assuming you've properly set up foreign keys, IDs and/or UNIQUE indexes (MySQL will notice no matching rows in the table and "ignore it" - selecting a NULL row). I haven't tested it quite properly, since I lack any data and had to assume, but the snippet should be basically functioning.

I would, however, like to mention that I am personally wary of having to do data-driven joins. The correct way to do normalisation in this case would be to find the largest common set of attributes and put them into an activities table. If then necessary, you could add extra information to adjoining tables. Generally speaking, however, if multiple tables use the same data, you should probably move it into the main column, unless it's absolutely necessary not to.