Announcement

Announcement Module
Collapse
No announcement yet.

Twitter style followees feed subscription

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Twitter style followees feed subscription

    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.

  • #2
    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.

    Comment

    Working...
    X