Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2010
    Posts
    1

    Unanswered: Twitter style followees feed subscriptions

    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. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •