Geek Answers Handbook

Activity feed with PHP MySQL

I am creating some kind of "activity stream / feed" using PHP and MySQL.

I have one table for a stream that just simply stores the activity type and timestamp. Then I need to find the actual data for the activity, which can be found in different tables (depending on the type of activity). A thread must be able to handle hundreds of users, and so I need it to work well. Parameters I received:

  • Get the flow table - pass through PHP with some kind of switch, and then call SQL for the corresponding activity data (for each action). (Slow)
  • Make one big SQL statement with some joins that accept all activity data in one query. Still making the switch to PHP, but without having to call SQL again. (Is it slow for large 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 it in PHP arrays, and from there the switching happens.

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

+4
source share
4 answers

You can execute a data-driven JOIN with a single request. Essentially, you would JOIN each subtable you need, and then select by smoothing the contents of the table you need. Assuming 1 is an Activity_drink, 2 is an Activity_eat, and 3 is an active_sport, and all the sub-tables have the content column that you want to get:

 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 will basically denormalize your tables at a specific time. You can also convert it to VIEW for easy access. This should not be too expensive, assuming that you have correctly configured the foreign keys, identifiers and / or UNIQUE indexes (MySQL will not see the corresponding rows in the table and will “ignore them” - selecting the NULL row). I did not test it quite correctly, since I did not have enough data and it should have been assumed, but the fragment should be basically functional.

However, I would like to mention that I personally fear that you should connect to the data. The correct way to normalize in this case is to find the largest common set of attributes and place them in the activities table. If necessary, you can add additional information to adjacent tables. Generally speaking, however, if the same data is used in several tables, you should probably transfer them to the main column if absolutely necessary.

+4
source

If the activity flow is not too long (several records, not thousands), I would go for only one query, if the tables were indexed correctly, the query should be fast enough. In case you can create alliances with your tables, I think this query should not be so expensive.

 SELECT * FROM ( SELECT * FROM stream INNER JOIN type1 ON stream.id = type1.stream_id LIMIT 50 UNION SELECT * FROM stream INNER JOIN type1 ON stream.id = type1.stream_id LIMIT 50 UNION ... ) ORDER BY stream.timestamp_field LIMIT 50

As another solution, if you have few type tables, you can query for each type of table and then combine the results. But you will have to order the combined results using PHP.

 $sql1 = "SELECT * FROM stream INNER JOIN type1 ON stream.id = type1.stream_id"; $sql2 = "SELECT * FROM stream INNER JOIN type2 ON stream.id = type2.stream_id"; ...

What size do you want to get? How do you want to order the results? performance may not be a problem limiting query results.

0
source

This is a late answer, but if the activity feed is part of a larger application, it might make sense to use an external service such as Collabinate ( http://www.collabinate.com ).

0
source

In my implemented solution, there are 7 different types of content that are stored in separate tables, but I have 1 activity / feed table .

My solution - Using mySQL5.7, I use the JSON field . I have a job that selects different types of content and inserts a row into the action table, and using mysql JSON_OBJECT () , it stores this data as json in the data column, and then my application manages that json and displays it as it should. JSON data has only relevant data for this type of content, so null values

  • id | contentType | data (json) | created | status

So, this means that I have no internal joins, no scalable table with lots of null fields.

I could easily add more types of content without changing anything.

0
source

More articles:


All Articles