Re: slow self-join query

On Sun, Mar 18, 2012 at 08:30, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> Why are you joining twice to the parent table?  If you're trying to
> recurse without a with clause, then wouldn't you join the last table
> to the one before it?

I'm FAR from being an SQL expert; there's a significant chance that
I'm not thinking about this right.  My intention for this query
(slightly renamed since the original post):

    SELECT F2.leader_id as leader_id, COUNT(U1.id) AS count
      FROM users AS U1
INNER JOIN user_associations AS F1 ON F1.follower_id = U1.id
INNER JOIN user_associations AS F2 ON F2.follower_id = U1.id
     WHERE F1.leader_id = 321
  GROUP BY F2.leader_id

is "among users that follow leader 321, who are the most widely
followed leaders?", or more formally, find all the users that are
followers of user 321 (via inner join on F1)  Of those users, tally up
their leaders so we know which leaders are most popular.  Recall that
the user_associations table is simply a user-to-user association:

 create_table "user_associations", :force => true do |t|
  t.integer  "follower_id"
  t.integer  "leader_id"
 end

Is there a better way to do this?