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.