Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Twitter style followees feed subscription

MoodangMoodang EntrantCurrent User Role Beginner
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.

Comments

  • xaprbxaprb Mentor Inactive User Role Beginner
    Why are you forcing the index choice? I think the combination of a forced index and an order-by looks like the data is either not indexed right, or can't be indexed well and mysql is the wrong tool.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.