John Davidson

Message:

Let's assume a user is following thousands of other people,

These people send news regularly, and in his/her page, our user wants to see the recent news (paginated) from these people only.

What is the most efficient way to do this?

This is what I'm doing currently:

1. Create a table called following in database, each follow is added here, id, user_id, following_user_id


2. Get a list of user's following_user_ids


3. fetch all news WHERE user_id (news poster id) is IN(...following_user_ids...)

For example if our user's id is 1:

SELECT `following_user_id` FROM `following` WHERE `user_id` = 1; /* This is used in the IN() below */



SELECT * FROM `news` WHERE `user_id` IN (4,11,7,...following_user_ids....) ORDER BY `id` DESC limit 50 offset 0



/* Of course the `user_id` is indexed in the `news` table */

But if the user is following thousands of people and the news table is huge, I'm assuming the IN (... thousands of IDs ...) will be very slow?

So, is there a more efficient way to do this?

EDIT:

In case any one also has this issue, just stick with the IN method, it is a lot faster than JOIN in my case.

---

My Answer:
One possible solution to improve the efficiency of fetching news from thousands of followed users is to use a JOIN instead of the IN clause. This can help reduce the number of individual queries being executed and potentially improve performance.

Here's an example of how you can modify your query to use a JOIN:

php
SELECT n.*
FROM news n
JOIN following f ON n.user_id = f.following_user_id
WHERE f.user_id = 1
ORDER BY n.id DESC
LIMIT 50 OFFSET 0;


In this query, we are joining the `news` table with the `following` table on the `user_id` and `following_user_id` columns. This will only return news from users that the current user is following.

Make sure to have proper indexes on the `user_id` and `following_user_id` columns in the `following` table to ensure optimal performance.

You can also consider implementing caching mechanisms or optimizing your database schema to further improve the performance of fetching news from followed users.