Dear All,

I am trying to implement Twitter style followee feed subscriptions for my users.

For example, If I want to select the most recent 25 feeds by the people I am following:

SELECT * FROM activities FORCE INDEX (site_id_is_hidden_user_id_created_at_090425) WHERE site_id = 1 AND is_hidden = 0 AND user_id IN (1, 2, 5, 10, 11, 21) ORDER BY created_at LIMIT 25;

With IN() in there, filesort is used for the ORDER BY part, resulting in a seriously long query.

Whereas if I just have user_id = 1 then this query takes 0 seconds.

So now if I try this:

((SELECT * FROM activities FORCE INDEX (site_id_is_hidden_user_id_created_at_090425) WHERE site_id = 1 AND is_hidden = 0 AND user_id = 1 ORDER BY created_at LIMIT 25) UNION
(SELECT * FROM activities FORCE INDEX (site_id_is_hidden_user_id_created_at_090425) WHERE site_id = 1 AND is_hidden = 0 AND user_id = 2 ORDER BY created_at LIMIT 25) UNION
(SELECT * FROM activities FORCE INDEX (site_id_is_hidden_user_id_created_at_090425) WHERE site_id = 1 AND is_hidden = 0 AND user_id = 5 ORDER BY created_at LIMIT 25) UNION
(SELECT * FROM activities FORCE INDEX (site_id_is_hidden_user_id_created_at_090425) WHERE site_id = 1 AND is_hidden = 0 AND user_id = 10 ORDER BY created_at LIMIT 25) UNION
(SELECT * FROM activities FORCE INDEX (site_id_is_hidden_user_id_created_at_090425) WHERE site_id = 1 AND is_hidden = 0 AND user_id = 11 ORDER BY created_at LIMIT 25) UNION
(SELECT * FROM activities FORCE INDEX (site_id_is_hidden_user_id_created_at_090425) WHERE site_id = 1 AND is_hidden = 0 AND user_id = 21 ORDER BY created_at LIMIT 25)) ORDER BY created_at LIMIT 25

Then the query is relatively much much quicker.

But now, the problem really hits me when some of the users start following 100-200 other users. So this becomes a union of 100-200 queries.

Can someone please recommend the right way to do this? I am sure I am not doing this the right way.

here's an outrageous idea...

you're retrieving only 25 result rows, right?

ditch the ORDER BY clause and sort them in your application language